全文总结
编译
1、编译过程主要是pl_gram.y做语法匹配的过程plpgsql_yyparse，整体匹配后的结果会作为PLpgSQL_stmt_block结构记录在plpgsql_parse_result中。
2、PLpgSQL_stmt_block结构分别记录两个核心List：body和exception，两个链表会记录所有执行节点（普通语句串到body后，when…then后的串到exception后）
3、编译过程会记录符号表：单链表ns_top记录所有使用的符号。
4、编译过程会记录变量值：数组plpgsql_Datums（数组个数plpgsql_nDatums），记录所有变量。
执行
1、执行过程整体框架在exec_stmt_block函数中
2、如果有异常处理：PG_TRY --> exec_stmts(estate, block->body) --> PG_CATCH --> exec_stmts(estate, exception->action)
3、如果没有异常处理：exec_stmts(estate, block->body)
分析实例
drop table tf1;
create table tf1(c1 int, c2 int, c3 varchar(32), c4 varchar(32), c5 int);
insert into tf1 values(1,1000, 'China','Dalian', 23000);
insert into tf1 values(2,4000, 'Janpan', 'Tokio', 45000);
insert into tf1 values(3,1500, 'China', 'Xian', 25000);
insert into tf1 values(4,300, 'China', 'Changsha', 24000);
insert into tf1 values(5,400,'USA','New York', 35000);
insert into tf1 values(6,5000, 'USA', 'Bostom', 15000);
======================================
PG
======================================
CREATE OR REPLACE FUNCTION tfun1(p3 text, x3 int[]) RETURNS text AS $$
DECLARE
row1 tf1%ROWTYPE;
row2 tf1%ROWTYPE;
txt1 text;
x1 int[];
n1 int := 1;
BEGIN
RAISE NOTICE 'start division_by_zero n1';
n1 = n1 / 0;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE 'caught division_by_zero do nothing';
RAISE NOTICE 'start print x1';
FOREACH x1 SLICE 1 IN ARRAY $2
LOOP
RAISE NOTICE 'row = %', x1;
END LOOP;
RAISE NOTICE 'start connecting c4';
txt1 = p3 || ':';
FOR row1 IN SELECT * FROM tf1 WHERE c3 = p3
LOOP
row2.c4 = row1.c4;
txt1 = txt1 || row2.c4 ||'+';
END LOOP;
return txt1;
END;
$$ LANGUAGE plpgsql;
select tfun1('China', ARRAY[[1,2,3],[4,5,6],[7,8,9],[10,11,12]]);
执行结果
postgres=# select tfun1('China', ARRAY[[1,2,3],[4,5,6],[7,8,9],[10,11,12]]);
NOTICE: start division_by_zero n1
NOTICE: caught division_by_zero do nothing
NOTICE: start print x1
NOTICE: row = {1,2,3}
NOTICE: row = {4,5,6}
NOTICE: row = {7,8,9}
NOTICE: row = {10,11,12}
NOTICE: start connecting c4
tfun1
-----------------------------
China:Dalian+Xian+Changsha+
1 编译
CreateFunction
ProcessUtilitySlow
CreateFunction
QualifiedNameGetCreationNamespace ：【1】解析namespace名称
pg_namespace_aclcheck ：【2】权限检查
compute_attributes_sql_style ：【3】解析函数体外部as、LANGUAGE等参数构造成的DefElem结构
（参数是哪来的？gram.y中匹配出来的）
=====================================
gram.y：
=====================================
createfunc_opt_item:
AS func_as
{
$$ = makeDefElem("as", (Node *)$2, @1);
}
| LANGUAGE NonReservedWord_or_Sconst
{
$$ = makeDefElem("language", (Node *)makeString($2), @1);
}
| TRANSFORM transform_type_list
{
$$ = makeDefElem("transform", (Node *)$2, @1);
}
| WINDOW
{
$$ = makeDefElem("window", (Node *)makeInteger(true), @1);
}
| common_func_opt_item
{
$$ = $1;
}
;
DefElem *
makeDefElem(char *name, Node *arg, int location)
{
DefElem *res = makeNode(DefElem);
res->defnamespace = NULL;
res->defname = name;
res->arg = arg;
res->defaction = DEFELEM_UNSPEC;
res->location = location;
return res;
}
=====================================
SearchSysCache1(LANGNAME, PointerGetDatum(language)) ：【3】在系统表pg_language中匹配语言、权限检查
interpret_function_parameter_list ：【4】构造入参列表，入参参数检查
compute_return_type ：【5】构造返回参数列表，检查返回值参数
ProcedureCreate
ProcedureCreate
ProcedureCreate
...
heap_modify_tuple ：构造一行插入pg_proc
...
/* Verify function body */
OidFunctionCall1(languageValidator, ObjectIdGetDatum(retval)) ：走语言提供的验证器开始验证函数内容
FunctionCall1Coll
FunctionCallInvoke
plpgsql_validator ：只传一个PROC的OID进来，在去系统表里拿这一行
// SPI start ：打开SPI可以在函数内执行SQL，编译可能不需要
plpgsql_compile(fake_fcinfo, true) ：开始编译函数体
// SPI end
plpgsql_compile
第一阶段参数解析
【1】初始化PLpgSQL_nsitem *ns_top
【2】ns_top装入函数名
【3】初始化128个PLpgSQL_datum
【4】初始化 出参数、入参数
【5】用plpgsql_build_datatype的结果构造variable，计入全局datum list数组
plpgsql_compile
function = plpgsql_HashTableLookup(&hashkey) ：已经编译过的函数信息记录进入hash表，构造key可以快速查询
do_compile ：开始编译
prosrcdatum = SysCacheGetAttr(PROCOID...) ：从系统表拿proc
proc_source = TextDatumGetCString(prosrcdatum) ：拿到源码
memset(function, 0, sizeof(PLpgSQL_function)); ：清空PLpgSQL_function重新构造
plpgsql_ns_init(); ：【1】初始化PLpgSQL_nsitem *ns_top
plpgsql_ns_push(NameStr(procStruct->proname)...) ：【2】ns_top装入函数名{itemtype = PLPGSQL_NSTYPE_LABEL, itemno = 0, prev = 0x0, name = 0x2ef8118 "tfun1"}
plpgsql_start_datums() ：【3】初始化128个PLpgSQL_datum
：plpgsql_nDatums：当前位置
：plpgsql_Datums：数组
switch (function->fn_is_trigger)
PLPGSQL_NOT_TRIGGER ：触发器fn走单独的分支，这里是普通函数
in_arg_varnos = (int *) palloc(numargs * sizeof(int)) ：【4】初始化 出参数、入参数
out_arg_variables = (PLpgSQL_variable **) palloc(numargs * sizeof(PLpgSQL_variable *))
for (i = 0; i < numargs; i++)
argdtype = plpgsql_build_datatype
argvariable = plpgsql_build_variable ：【5】用plpgsql_build_datatype的结果构造variable，计入全局datum list数组
：循环结束，参数里面的变量添加完毕
：ns_top和val状态是什么？
var = plpgsql_build_variable("found"...) ：构造found变量
：全局ns_top和var各新增一条
parse_rc = plpgsql_yyparse() ：开始解析
第二阶段函数体解析——声明部分
（这里可以用gdb断这个pl_gram.y的函数了）
【1】开始pl_gram.y解析，这里可以用gdb断这个文件的函数了
【2】解析参数声明部分
【】解析其他部分
【3】所有的结果在gram.y中一级一级的汇总到$$中，最后在父节点传给全局变量plpgsql_parse_result，所有节点都用List挂在body上。
do_compile
...
parse_rc = plpgsql_yyparse(); ：【1】开始pl_gram.y解析，这里可以用gdb断这个文件的函数了
function->action = plpgsql_parse_result; ：【3】所有的结果在gram.y中一级一级的汇总到$$中，最后在父节点传给全局变量plpgsql_parse_result
(pl_gram.y语法树截取)
decl_datatype : ：【2】解析参数声明部分
{
$$ = read_datatype(yychar); ：read_datatype构造数据记录到plpgsql_Datums、ns_top
yyclearin;
}
;
第三阶段函数体解析——第一句：RAISE NOTICE 'start division_by_zero n1';
总结：解析树拼PLpgSQL_stmt_raise然后返回！
stmt_raise : K_RAISE
{
PLpgSQL_stmt_raise *new;
int tok;
new = palloc(sizeof(PLpgSQL_stmt_raise));
new->cmd_type = PLPGSQL_STMT_RAISE;
new->lineno = plpgsql_location_to_lineno(@1);
new->stmtid = ++plpgsql_curr_compile->nstatements;
new->elog_level = ERROR;
new->condname = NULL;
new->message = NULL;
new->params = NIL;
new->options = NIL;
tok = yylex();
...
if (tok != ';')
{
...
...
else if (tok_is_keyword(tok, &yylval,
K_NOTICE, "notice"))
{
new->elog_level = NOTICE;
tok = yylex();
}
...
...
...
if (tok == SCONST)start division_by_zero n1
{
new->message = yylval.str;
tok = yylex();
if (tok != ',' && tok != ';' && tok != K_USING)
yyerror("syntax error");
...
check_raise_parameters(new);
$$ = (PLpgSQL_stmt *)new;
}
;
第三阶段函数体解析——第二句：n1 = n1 / 0;
总结：解析树拼PLpgSQL_stmt_assign然后返回！
stmt_assign : T_DATUM
{
PLpgSQL_stmt_assign *new;
RawParseMode pmode;
switch ($1.ident ? 1 : list_length($1.idents))
{
case 1:
pmode = RAW_PARSE_PLPGSQL_ASSIGN1;
break;
case 2:
pmode = RAW_PARSE_PLPGSQL_ASSIGN2;
break;
case 3:
pmode = RAW_PARSE_PLPGSQL_ASSIGN3;
break;
default:
elog(ERROR, "unexpected number of names");
pmode = 0;
}
check_assignable($1.datum, @1);
new = palloc0(sizeof(PLpgSQL_stmt_assign));
new->cmd_type = PLPGSQL_STMT_ASSIGN;
new->lineno = plpgsql_location_to_lineno(@1);
new->stmtid = ++plpgsql_curr_compile->nstatements;
new->varno = $1.datum->dno;
plpgsql_push_back_token(T_DATUM);
new->expr = read_sql_construct(';', 0, 0, ";",
pmode,
false, true, true,
NULL, NULL);
$$ = (PLpgSQL_stmt *)new;
}
;
返回值：
(gdb) p *new
$58 = {cmd_type = PLPGSQL_STMT_ASSIGN, lineno = 11, stmtid = 2, varno = 7, expr = 0x2f2e710}
(gdb) p *new->expr
$59 = {
query = 0x2f2e7a8 "n1 = n1 / 0",
parseMode = RAW_PARSE_PLPGSQL_ASSIGN1,
plan = 0x0, paramnos = 0x0, func = 0x0, ns = 0x2f2e480,
expr_simple_expr = 0x0, expr_simple_type = 0,
expr_simple_typmod = 0, expr_simple_mutable = false,
target_param = -1,
expr_rw_param = 0x0, expr_simple_plansource = 0x0,
expr_simple_plan = 0x0, expr_simple_plan_lxid = 0,
expr_simple_state = 0x0, expr_simple_in_use = false,
expr_simple_lxid = 0}
第三阶段函数体解析——第三句： EXCEPTION WHEN division_by_zero THEN ...
第一步：遇到EXCEPTION不管后面是什么，先新建连个变量：
b plpgsql_yyparse
b tok_is_keyword
b plpgsql_build_variable
b read_datatype
语法解析：
exception_sect :
{ $$ = NULL; }
| K_EXCEPTION
{
int lineno = plpgsql_location_to_lineno(@1);
PLpgSQL_exception_block *new = palloc(sizeof(PLpgSQL_exception_block));
PLpgSQL_variable *var;
var = plpgsql_build_variable("sqlstate", lineno,
plpgsql_build_datatype(TEXTOID,
-1,
plpgsql_curr_compile->fn_input_collation,
NULL),
true);
var->isconst = true;
new->sqlstate_varno = var->dno;
var = plpgsql_build_variable("sqlerrm", lineno,
plpgsql_build_datatype(TEXTOID,
-1,
plpgsql_curr_compile->fn_input_collation,
NULL),
true);
var->isconst = true;
new->sqlerrm_varno = var->dno;
$<exception_block>$ = new;
}
proc_exceptions
{
PLpgSQL_exception_block *new = $<exception_block>2;
new->exc_list = $3;
$$ = new;
}
;
第二步：解析EXCEPTION后面部分
proc_exceptions : proc_exceptions proc_exception
{
$$ = lappend($1, $2);
}
| proc_exception
{
$$ = list_make1($1);
}
;
proc_exception : K_WHEN proc_conditions K_THEN proc_sect
{
PLpgSQL_exception *new;
new = palloc0(sizeof(PLpgSQL_exception));
new->lineno = plpgsql_location_to_lineno(@1);
new->conditions = $2;
new->action = $4;
$$ = new;
}
;
proc_conditions : proc_conditions K_OR proc_condition
{
PLpgSQL_condition *old;
for (old = $1; old->next != NULL; old = old->next)
;
old->next = $3;
$$ = $1;
}
| proc_condition
{
$$ = $1;
}
;
proc_condition : any_identifier
{
if (strcmp($1, "sqlstate") != 0)
{
$$ = plpgsql_parse_err_condition($1); ：走这里解析非sqlstate xxx格式的异常例如 division_by_zero
} ：exception_label_map[i]里面找division_by_zero，匹配了就构造一个PLpgSQL_condition返回
else
{
PLpgSQL_condition *new;
char *sqlstatestr;
if (yylex() != SCONST)
yyerror("syntax error");
sqlstatestr = yylval.str;
if (strlen(sqlstatestr) != 5)
yyerror("invalid SQLSTATE code");
if (strspn(sqlstatestr, "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ") != 5)
yyerror("invalid SQLSTATE code");
new = palloc(sizeof(PLpgSQL_condition));
new->sqlerrstate =
MAKE_SQLSTATE(sqlstatestr[0],
sqlstatestr[1],
sqlstatestr[2],
sqlstatestr[3],
sqlstatestr[4]);
new->condname = sqlstatestr;
new->next = NULL;
$$ = new;
}
}
;
第三阶段函数体解析——第四句： FOREACH x1 SLICE 1 IN ARRAY $2 ...
FOREACH x1 SLICE 1 IN ARRAY $2
LOOP
RAISE NOTICE 'row = %', x1;
END LOOP;
stmt_foreach_a : opt_loop_label K_FOREACH for_variable foreach_slice K_IN K_ARRAY expr_until_loop loop_body
{
PLpgSQL_stmt_foreach_a *new;
new = palloc0(sizeof(PLpgSQL_stmt_foreach_a));
new->cmd_type = PLPGSQL_STMT_FOREACH_A;
new->lineno = plpgsql_location_to_lineno(@2);
new->stmtid = ++plpgsql_curr_compile->nstatements;
new->label = $1;
new->slice = $4;
new->expr = $7;
new->body = $8.stmts;
if ($3.row)
{
new->varno = $3.row->dno;
check_assignable($3.row, @3);
}
else if ($3.scalar)
{
new->varno = $3.scalar->dno;
check_assignable($3.scalar, @3);
}
else
{
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("loop variable of FOREACH must be a known variable or list of variables"),
parser_errposition(@3)));
}
check_labels($1, $8.end_label, $8.end_label_location);
plpgsql_ns_pop();
$$ = (PLpgSQL_stmt *) new;
}
;
datums没变，目前ns_top状态：
p *ns_top
pop出去了
$9 = {itemtype = PLPGSQL_NSTYPE_VAR, itemno = 9, prev = 0x2edb2b0, name = 0x2f15468 "sqlerrm"}
$10 = {itemtype = PLPGSQL_NSTYPE_VAR, itemno = 8, prev = 0x2edb0a0, name = 0x2edb2c0 "sqlstate"}
$25 = {itemtype = PLPGSQL_NSTYPE_VAR, itemno = 7, prev = 0x2f2dac0, name = 0x2f2e490 "n1"}
$27 = {itemtype = PLPGSQL_NSTYPE_VAR, itemno = 6, prev = 0x2f366e8, name = 0x2f2dad0 "x1"}
$28 = {itemtype = PLPGSQL_NSTYPE_VAR, itemno = 5, prev = 0x2f36340, name = 0x2f366f8 "txt1"}
$29 = {itemtype = PLPGSQL_NSTYPE_REC, itemno = 4, prev = 0x2f36108, name = 0x2f36350 "row2"}
$30 = {itemtype = PLPGSQL_NSTYPE_REC, itemno = 3, prev = 0x2f35ed0, name = 0x2f36118 "row1"}
$31 = {itemtype = PLPGSQL_NSTYPE_LABEL, itemno = 0, prev = 0x2f35e98, name = 0x2f35ee0 ""}
$32 = {itemtype = PLPGSQL_NSTYPE_VAR, itemno = 2, prev = 0x2f35d30, name = 0x2f35ea8 "found"}
$33 = {itemtype = PLPGSQL_NSTYPE_VAR, itemno = 1, prev = 0x2f35cf8, name = 0x2f35d40 "x3"}
$34 = {itemtype = PLPGSQL_NSTYPE_VAR, itemno = 1, prev = 0x2f35b90, name = 0x2f35d08 "$2"}
$35 = {itemtype = PLPGSQL_NSTYPE_VAR, itemno = 0, prev = 0x2f35b58, name = 0x2f35ba0 "p3"}
$36 = {itemtype = PLPGSQL_NSTYPE_VAR, itemno = 0, prev = 0x2f35a88, name = 0x2f35b68 "$1"}
$37 = {itemtype = PLPGSQL_NSTYPE_LABEL, itemno = 0, prev = 0x0, name = 0x2f35a98 "tfun1"}
第三阶段函数体解析——第五句： txt1 = p3 || ':';
拼接一个PLpgSQL_stmt_assign结构
stmt_assign : T_DATUM
{
PLpgSQL_stmt_assign *new;
RawParseMode pmode;
switch ($1.ident ? 1 : list_length($1.idents))
{
case 1:
pmode = RAW_PARSE_PLPGSQL_ASSIGN1;
break;
case 2:
pmode = RAW_PARSE_PLPGSQL_ASSIGN2;
break;
case 3:
pmode = RAW_PARSE_PLPGSQL_ASSIGN3;
break;
default:
elog(ERROR, "unexpected number of names");
pmode = 0;
}
check_assignable($1.datum, @1);
new = palloc0(sizeof(PLpgSQL_stmt_assign));
new->cmd_type = PLPGSQL_STMT_ASSIGN;
new->lineno = plpgsql_location_to_lineno(@1);
new->stmtid = ++plpgsql_curr_compile->nstatements;
new->varno = $1.datum->dno;
plpgsql_push_back_token(T_DATUM);
new->expr = read_sql_construct(';', 0, 0, ";",
pmode,
false, true, true,
NULL, NULL);
$$ = (PLpgSQL_stmt *)new;
}
;
返回PLpgSQL_stmt_assign
(gdb) p new
$15 = (PLpgSQL_stmt_assign *) 0x2f15c00
(gdb) p *new
$16 = {cmd_type = PLPGSQL_STMT_ASSIGN, lineno = 23, stmtid = 8, varno = 5, expr = 0x2f15c98}
(gdb) p *new->expr
$17 = {
query = 0x2f15d30 "txt1 = p3 || ':'",
parseMode = RAW_PARSE_PLPGSQL_ASSIGN1,
plan = 0x0, paramnos = 0x0, func = 0x0,
ns = 0x2f15458, expr_simple_expr = 0x0,
expr_simple_type = 0, expr_simple_typmod = 0, expr_simple_mutable = false, target_param = -1,
expr_rw_param = 0x0, expr_simple_plansource = 0x0, expr_simple_plan = 0x0, expr_simple_plan_lxid = 0,
expr_simple_state = 0x0, expr_simple_in_use = false, expr_simple_lxid = 0}
(gdb) p *new->expr->ns
$18 = {itemtype = PLPGSQL_NSTYPE_VAR, itemno = 9, prev = 0x2edb2b0, name = 0x2f15468 "sqlerrm"}
第三阶段函数体解析——第六句： FOR...LOOP...END LOOP
for语法解析
for_variable : T_DATUM
{
$$.name = NameOfDatum(&($1));
$$.lineno = plpgsql_location_to_lineno(@1);
if ($1.datum->dtype == PLPGSQL_DTYPE_ROW ||
$1.datum->dtype == PLPGSQL_DTYPE_REC)
{
$$.scalar = NULL;
$$.row = $1.datum;
}
else
{
int tok;
$$.scalar = $1.datum;
$$.row = NULL;
tok = yylex();
plpgsql_push_back_token(tok);
if (tok == ',')
$$.row = (PLpgSQL_datum *)
read_into_scalar_list($$.name,
$$.scalar,
@1);
}
}
| T_WORD
{
int tok;
$$.name = $1.ident;
$$.lineno = plpgsql_location_to_lineno(@1);
$$.scalar = NULL;
$$.row = NULL;
tok = yylex();
plpgsql_push_back_token(tok);
if (tok == ',')
word_is_not_variable(&($1), @1);
}
| T_CWORD
{
cword_is_not_variable(&($1), @1);
}
;
编译结果分析（通过plpgsql_parse_result返回）
代码位置
do_compile
...
...
parse_rc = plpgsql_yyparse();
if (parse_rc != 0)
elog(ERROR, "plpgsql parser returned %d", parse_rc);
function->action = plpgsql_parse_result;
plpgsql_scanner_finish();
pfree(proc_source);
检查plpgsql_parse_result结构
猜猜为什么body只有两个stmt？？
(gdb) p *plpgsql_parse_result
$31 = {
cmd_type = PLPGSQL_STMT_BLOCK, lineno = 8, stmtid = 13, label = 0x0,
body = 0x2edb1e0, n_initvars = 5, initvarnos = 0x2edb0d8, exceptions = 0x2f151c8}
(gdb) p *plpgsql_parse_result->initvarnos
$38 = 3
===========================
body
===========================
(gdb) p *plpgsql_parse_result->body
$34 = {type = T_List, length = 2, max_length = 5, elements = 0x2edb1f8, initial_elements = 0x2edb1f8}
(gdb) tr PLpgSQL_stmt plpgsql_parse_result->body->elements[0]
$59 = {cmd_type = PLPGSQL_STMT_RAISE, lineno = 10, stmtid = 1}
(gdb) tr PLpgSQL_stmt_raise plpgsql_parse_result->body->elements[0]
$61 = {cmd_type = PLPGSQL_STMT_RAISE, lineno = 10, stmtid = 1, elog_level = 18, condname = 0x0,
message = 0x2edb1a8 "start division_by_zero n1", params = 0x0, options = 0x0}
(gdb) tr PLpgSQL_stmt plpgsql_parse_result->body->elements[1]
$60 = {cmd_type = PLPGSQL_STMT_ASSIGN, lineno = 11, stmtid = 2}
(gdb) p *((PLpgSQL_stmt_assign*)plpgsql_parse_result->body->elements[1])->expr
$67 = {query = 0x2f15178 "n1 = n1 / 0", parseMode = RAW_PARSE_PLPGSQL_ASSIGN1, plan = 0x0, paramnos = 0x0, func = 0x0, ns = 0x2edb0a0,
expr_simple_expr = 0x0, expr_simple_type = 0, expr_simple_typmod = 0, expr_simple_mutable = false, target_param = -1,
expr_rw_param = 0x0, expr_simple_plansource = 0x0, expr_simple_plan = 0x0, expr_simple_plan_lxid = 0, expr_simple_state = 0x0,
expr_simple_in_use = false, expr_simple_lxid = 0}
(gdb) tr PLpgSQL_stmt_assign plpgsql_parse_result->body->elements[1]
$65 = {cmd_type = PLPGSQL_STMT_ASSIGN, lineno = 11, stmtid = 2, varno = 7, expr = 0x2f150e0}
===========================
exceptions
===========================
(gdb) p *plpgsql_parse_result->exceptions
$33 = {sqlstate_varno = 8, sqlerrm_varno = 9, exc_list = 0x2f16780}
因为按照上面的写法，后面执行的foreach等stmt都挂在exctption里面了！
检查ns_top
编译时用到的都删了，就剩下函数明确定义的符号 和 隐含符号$1 $2 found
$32 = {itemtype = PLPGSQL_NSTYPE_VAR, itemno = 2, prev = 0x2f35d30, name = 0x2f35ea8 "found"}
$33 = {itemtype = PLPGSQL_NSTYPE_VAR, itemno = 1, prev = 0x2f35cf8, name = 0x2f35d40 "x3"}
$34 = {itemtype = PLPGSQL_NSTYPE_VAR, itemno = 1, prev = 0x2f35b90, name = 0x2f35d08 "$2"}
$35 = {itemtype = PLPGSQL_NSTYPE_VAR, itemno = 0, prev = 0x2f35b58, name = 0x2f35ba0 "p3"}
$36 = {itemtype = PLPGSQL_NSTYPE_VAR, itemno = 0, prev = 0x2f35a88, name = 0x2f35b68 "$1"}
$37 = {itemtype = PLPGSQL_NSTYPE_LABEL, itemno = 0, prev = 0x0, name = 0x2f35a98 "tfun1"}
检查datum
p *((PLpgSQL_var*)plpgsql_Datums[0])
$139 = {
dtype = PLPGSQL_DTYPE_VAR, dno = 0, refname = 0x2ef81b8 "p3",
lineno = 0, isconst = false, notnull = false, default_val = 0x0,
datatype = 0x2ef8140, cursor_explicit_expr = 0x0, cursor_explicit_argrow = 0,
cursor_options = 0, value = 0, isnull = true, freeval = false, promise = PLPGSQL_PROMISE_NONE}
$137 = {dtype = PLPGSQL_DTYPE_VAR, dno = 1}
p *((PLpgSQL_var*)plpgsql_Datums[1])
$140 = {
dtype = PLPGSQL_DTYPE_VAR, dno = 1, refname = 0x2ef8358 "x3",
lineno = 0, isconst = false, notnull = false, default_val = 0x0,
datatype = 0x2ef8248, cursor_explicit_expr = 0x0, cursor_explicit_argrow = 0,
cursor_options = 0, value = 0, isnull = true, freeval = false, promise = PLPGSQL_PROMISE_NONE}
$143 = {itemtype = PLPGSQL_NSTYPE_VAR, itemno = 2, prev = 0x2ef83b0, name = 0x2ef8528 "found"}
p *((PLpgSQL_var*)plpgsql_Datums[2])
$149 = {
dtype = PLPGSQL_DTYPE_VAR, dno = 2, refname = 0x2ef84f8 "found",
lineno = 0, isconst = false, notnull = false, default_val = 0x0,
datatype = 0x2ef83e8, cursor_explicit_expr = 0x0, cursor_explicit_argrow = 0,
cursor_options = 0, value = 0, isnull = true, freeval = false, promise = PLPGSQL_PROMISE_NONE}
p *((PLpgSQL_var*)plpgsql_Datums[3])
$19 = {
dtype = PLPGSQL_DTYPE_REC, dno = 3, refname = 0x2f360e8 "row1",
lineno = 3, isconst = false, notnull = false, default_val = 0x0,
datatype = 0x2f36018, cursor_explicit_expr = 0xffffffff00004004,
cursor_explicit_argrow = 0, cursor_options = 0, value = 9187201950435737470,
isnull = 8, freeval = false, promise = PLPGSQL_PROMISE_NONE}
p *((PLpgSQL_var*)plpgsql_Datums[4])
$20 = {
dtype = PLPGSQL_DTYPE_REC, dno = 4, refname = 0x2f36320 "row2",
lineno = 4, isconst = false, notnull = false, default_val = 0x0,
datatype = 0x2f36250, cursor_explicit_expr = 0xffffffff00004004,
cursor_explicit_argrow = 0, cursor_options = 0, value = 9187201950435737470,
isnull = 8, freeval = false, promise = PLPGSQL_PROMISE_NONE}
p *((PLpgSQL_var*)plpgsql_Datums[5])
$21 = {
dtype = PLPGSQL_DTYPE_VAR, dno = 5, refname = 0x2f2d268 "txt1",
lineno = 5, isconst = false, notnull = false, default_val = 0x0,
datatype = 0x2f2d158, cursor_explicit_expr = 0x0,
cursor_explicit_argrow = 0, cursor_options = 0, value = 0, isnull = true,
freeval = false, promise = PLPGSQL_PROMISE_NONE}
p *((PLpgSQL_var*)plpgsql_Datums[6])
$22 = {
dtype = PLPGSQL_DTYPE_VAR, dno = 6, refname = 0x2f2daa0 "x1",
lineno = 6, isconst = false, notnull = false, default_val = 0x0,
datatype = 0x2f2d990, cursor_explicit_expr = 0x0,
cursor_explicit_argrow = 0, cursor_options = 0, value = 0, isnull = true,
freeval = false, promise = PLPGSQL_PROMISE_NONE}
p *((PLpgSQL_var*)plpgsql_Datums[7])
$23 = {
dtype = PLPGSQL_DTYPE_VAR, dno = 7, refname = 0x2f2e460 "n1",
lineno = 7, isconst = false, notnull = false, default_val = 0x2f2e310,
datatype = 0x2f2e298, cursor_explicit_expr = 0x0,
cursor_explicit_argrow = 0, cursor_options = 0, value = 0,
isnull = true, freeval = false, promise = PLPGSQL_PROMISE_NONE}
(gdb) p *((PLpgSQL_var*)plpgsql_Datums[8])
$69 = {
dtype = PLPGSQL_DTYPE_VAR, dno = 8, refname = 0x2f2c920 "sqlstate",
lineno = 12, isconst = true, notnull = false,
default_val = 0x0, datatype = 0x2f2c810, cursor_explicit_expr = 0x0, c
ursor_explicit_argrow = 0, cursor_options = 0, value = 0,
isnull = true, freeval = false, promise = PLPGSQL_PROMISE_NONE}
(gdb) p *((PLpgSQL_var*)plpgsql_Datums[9])
$70 = {
dtype = PLPGSQL_DTYPE_VAR, dno = 9, refname = 0x2f2ca58 "sqlerrm",
lineno = 12, isconst = true, notnull = false,
default_val = 0x0, datatype = 0x2f2c948, cursor_explicit_expr = 0x0,
cursor_explicit_argrow = 0, cursor_options = 0, value = 0,
isnull = true, freeval = false, promise = PLPGSQL_PROMISE_NONE}
p *((PLpgSQL_var*)plpgsql_Datums[10])
$25 = {
dtype = PLPGSQL_DTYPE_RECFIELD, dno = 10, refname = 0x2f160c8 "c4",
lineno = 4, isconst = 255, notnull = 255, default_val = 0x1,
datatype = 0x0, cursor_explicit_expr = 0x0, cursor_explicit_argrow = 2139062142,
cursor_options = 2139062143,value = 9187201950435737471,
isnull = 8, freeval = false, promise = PLPGSQL_PROMISE_NONE}
(gdb) p *((PLpgSQL_var*)plpgsql_Datums[11])
$29 = {
dtype = PLPGSQL_DTYPE_RECFIELD, dno = 11, refname = 0x2f16260 "c4",
lineno = 3, isconst = 255, notnull = 255, default_val = 0x1,
datatype = 0x0, cursor_explicit_expr = 0x0, cursor_explicit_argrow = 2139062142,
cursor_options = 2139062143, value = 9187201950435737471,
isnull = 8, freeval = false, promise = PLPGSQL_PROMISE_NONE}
2 执行
sql
======================================
PG
======================================
CREATE OR REPLACE FUNCTION tfun1(p3 text, x3 int[]) RETURNS text AS $$
DECLARE
row1 tf1%ROWTYPE;
row2 tf1%ROWTYPE;
txt1 text;
x1 int[];
n1 int := 1;
BEGIN
RAISE NOTICE 'start division_by_zero n1';
n1 = n1 / 0;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE 'caught division_by_zero do nothing';
RAISE NOTICE 'start print x1';
FOREACH x1 SLICE 1 IN ARRAY $2
LOOP
RAISE NOTICE 'row = %', x1;
END LOOP;
RAISE NOTICE 'start connecting c4';
txt1 = p3 || ':';
FOR row1 IN SELECT * FROM tf1 WHERE c3 = p3
LOOP
row2.c4 = row1.c4;
txt1 = txt1 || row2.c4 ||'+';
END LOOP;
return txt1;
END;
$$ LANGUAGE plpgsql;
select tfun1('China', ARRAY[[1,2,3],[4,5,6],[7,8,9],[10,11,12]]);
上面SQL对应的执行顺序
plpgsql_call_handler
plpgsql_exec_function
exec_toplevel_block
exec_stmt_block
TRY
exec_stmts
...
case PLPGSQL_STMT_RAISE:
rc = exec_stmt_raise(estate, (PLpgSQL_stmt_raise *) stmt) ： RAISE NOTICE 'start division_by_zero n1';
case PLPGSQL_STMT_ASSIGN:
rc = exec_stmt_assign(estate, (PLpgSQL_stmt_assign *) stmt) ：n1 = n1 / 0;
PG_CATCH
foreach(e, block->exceptions->exc_list)
if (exception_matches_conditions(edata, exception->conditions))
exec_stmts(estate, exception->action)
...
case PLPGSQL_STMT_RAISE:
rc = exec_stmt_raise(estate, (PLpgSQL_stmt_raise *) stmt) ：RAISE NOTICE 'caught division_by_zero do nothing';
：RAISE NOTICE 'start print x1';
case PLPGSQL_STMT_FOREACH_A:
rc = exec_stmt_foreach_a(estate, (PLpgSQL_stmt_foreach_a *) stmt) ：FOREACH
exec_stmts ：RAISE NOTICE 'row = %', x1;
case PLPGSQL_STMT_RAISE:
rc = exec_stmt_raise(estate, (PLpgSQL_stmt_raise *) stmt) ：RAISE NOTICE 'start connecting c4';
case PLPGSQL_STMT_ASSIGN:
rc = exec_stmt_assign(estate, (PLpgSQL_stmt_assign *) stmt) ：txt1 = p3 || ':';
case PLPGSQL_STMT_FORS:
rc = exec_stmt_fors(estate, (PLpgSQL_stmt_fors *) stmt) ：FOR xxx IN
exec_stmts ：row2.c4 = row1.c4;
：txt1 = txt1 || row2.c4 ||'+';
case PLPGSQL_STMT_RETURN：
rc = exec_stmt_return(estate, (PLpgSQL_stmt_return *) stmt)
PG_END_TRY
这就是一个plpgsql编译执行的全流程。
|