Postgresql随手记（8）使用plpgsql解析游标定义子树的过程

mingjie

发布于 2022-07-14 13:51:54

3950

发布于 2022-07-14 13:51:54

例如有如下函数

create or replace procedure fun1(a in integer) as $$
declare
	cur cursor for select * from t10 order by f1;
begin
	...
end;
$$ LANGUAGE plpgsql ;

声明中游标是怎么解析的？

cur cursor for select * from t10 order by f1;

step1

语法树

decl_statement	: decl_varname decl_const decl_datatype decl_collate decl_notnull decl_defval
					{
						...
					}
/* 【第一步】 */
/* 执行到这里的时候decl_varname、opt_scrollable都解析完了 */
/* decl_varname只检查名字有没有使用过，不加入namespace */
/* opt_scrollable do nothing */
				| decl_varname opt_scrollable K_CURSOR
					{ plpgsql_ns_push($1.name, PLPGSQL_LABEL_OTHER); }
/* 这里加入namespace
$7 = {itemtype = PLPGSQL_NSTYPE_LABEL, itemno = 3, prev = 0x381ad50, name = 0x381add8 "cur"}
$2 = {itemtype = PLPGSQL_NSTYPE_LABEL, itemno = 0, prev = 0x381ad18, name = 0x381ad60 ""}
$3 = {itemtype = PLPGSQL_NSTYPE_VAR, itemno = 1, prev = 0x381ac48, name = 0x381ad28 "found"}
$4 = {itemtype = PLPGSQL_NSTYPE_VAR, itemno = 0, prev = 0x381ac10, name = 0x381ac58 "a_int"}
$5 = {itemtype = PLPGSQL_NSTYPE_VAR, itemno = 0, prev = 0x381ab40, name = 0x381ac20 "$1"}
$6 = {itemtype = PLPGSQL_NSTYPE_LABEL, itemno = 0, prev = 0x0, name = 0x381ab50 "demo10"}
*/

/* 解析后面的语句先要递归完成decl_cursor_args、decl_is_for、decl_cursor_query */
/* goto decl_cursor_args 继续阅读 */
				  decl_cursor_args decl_is_for decl_cursor_query
					{
						...
						plpgsql_ns_pop();
						...
						plpgsql_build_variable();
						...
						
					}

step2

decl_cursor_args 检查是普通语法还是复杂语法 curs2 CURSOR FOR SELECT * FROM tenk1; curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;

当前语句cur cursor for select * from t10 order by f1;属于简单语法

语法树

/* 【第二步】 */
decl_cursor_args :
					{
						$$ = NULL;
					}
				| '(' decl_cursor_arglist ')'
					{
						PLpgSQL_row *new;
						int i;
						ListCell *l;

						new = palloc0(sizeof(PLpgSQL_row));
						new->dtype = PLPGSQL_DTYPE_ROW;
						new->refname = "(unnamed row)";
						new->lineno = plpgsql_location_to_lineno(@1);
						new->rowtupdesc = NULL;
						new->nfields = list_length($2);
						new->fieldnames = palloc(new->nfields * sizeof(char *));
						new->varnos = palloc(new->nfields * sizeof(int));

						i = 0;
						foreach (l, $2)
						{
							PLpgSQL_variable *arg = (PLpgSQL_variable *) lfirst(l);
							Assert(!arg->isconst);
							new->fieldnames[i] = arg->refname;
							new->varnos[i] = arg->dno;
							i++;
						}
						list_free($2);

						plpgsql_adddatum((PLpgSQL_datum *) new);
						$$ = (PLpgSQL_datum *) new;
					}
				;

step3

decl_cursor_query

decl_cursor_query :
					{
						$$ = read_sql_stmt();
/*
read_sql_construct(';', 0, 0, ";", RAW_PARSE_DEFAULT,false, true, true, NULL, NULL);
  for (;;)
    tok = yylex()     275      T_WORD   select
    tok = yylex()     42       *        *
    tok = yylex()     325      K_FROM   from
    tok = yylex()     275      T_WORD   t10
    tok = yylex()     275      T_WORD   order
    tok = yylex()     288      K_BY     by
    tok = yylex()     275      T_WORD   f1
    tok = yylex()     59       ;        ;
  return
  {query = 0x381b330 "select * from t10 order by f1", 
  parseMode = RAW_PARSE_DEFAULT, plan = 0x0, paramnos = 0x0, 
  func = 0x0, ns = 0x381adc8, 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}
*/
					}
				;

step4

语法树，从【第四步】开始看

注意在第一步Push进去的cur plpgsql_ns_push($1.name, PLPGSQL_LABEL_OTHER) 在第四步开始就Pop出来了，这个符号主要是给decl_cursor_args、decl_is_for、decl_cursor_query使用的

decl_statement	: decl_varname decl_const decl_datatype decl_collate decl_notnull decl_defval
					{
						...
					}
/* 【第一步】 */
/* 执行到这里的时候decl_varname、opt_scrollable都解析完了 */
/* decl_varname只检查名字有没有使用过，不加入namespace */
/* opt_scrollable do nothing */
				| decl_varname opt_scrollable K_CURSOR
					{ plpgsql_ns_push($1.name, PLPGSQL_LABEL_OTHER); }
/* 这里加入namespace
$7 = {itemtype = PLPGSQL_NSTYPE_LABEL, itemno = 3, prev = 0x381ad50, name = 0x381add8 "cur"}
$2 = {itemtype = PLPGSQL_NSTYPE_LABEL, itemno = 0, prev = 0x381ad18, name = 0x381ad60 ""}
$3 = {itemtype = PLPGSQL_NSTYPE_VAR, itemno = 1, prev = 0x381ac48, name = 0x381ad28 "found"}
$4 = {itemtype = PLPGSQL_NSTYPE_VAR, itemno = 0, prev = 0x381ac10, name = 0x381ac58 "a_int"}
$5 = {itemtype = PLPGSQL_NSTYPE_VAR, itemno = 0, prev = 0x381ab40, name = 0x381ac20 "$1"}
$6 = {itemtype = PLPGSQL_NSTYPE_LABEL, itemno = 0, prev = 0x0, name = 0x381ab50 "demo10"}
*/

/* 解析后面的语句先要递归完成decl_cursor_args、decl_is_for、decl_cursor_query */
/* goto decl_cursor_args 继续阅读 */
/* 【第四步】 */
				  decl_cursor_args decl_is_for decl_cursor_query
					{
						...
						plpgsql_ns_pop();
						...
						// 下面函数说明中解释
						new = (PLpgSQL_var *) plpgsql_build_variable(...plpgsql_build_datatype()...);
						...
						curname_def = palloc0(sizeof(PLpgSQL_expr));

/* 注意new已经在plpgsql_Datums数组中了，最后不需要返回 */
/* 下面在做一些调整，就可以结束了 */
						/* Note: refname has been truncated to NAMEDATALEN */
						cp1 = new->refname;
						cp2 = buf;
						/*
						 * Don't trust standard_conforming_strings here;
						 * it might change before we use the string.
						 */
						if (strchr(cp1, '\\') != NULL)
							*cp2++ = ESCAPE_STRING_SYNTAX;
						*cp2++ = '\'';
						while (*cp1)
						{
							if (SQL_STR_DOUBLE(*cp1, true))
								*cp2++ = *cp1;
							*cp2++ = *cp1++;
						}
						strcpy(cp2, "'::pg_catalog.refcursor");
						curname_def->query = pstrdup(buf);
						curname_def->parseMode = RAW_PARSE_PLPGSQL_EXPR;
						new->default_val = curname_def;

						new->cursor_explicit_expr = $7;
						if ($5 == NULL)
							new->cursor_explicit_argrow = -1;
						else
							new->cursor_explicit_argrow = $5->dno;
						new->cursor_options = CURSOR_OPT_FAST_PLAN | $2;
					}
				;

函数说明

plpgsql_build_datatype

系统表查询类型OID，构造PLpgSQL_type

PLpgSQL_type *
plpgsql_build_datatype(Oid typeOid, int32 typmod,
					   Oid collation, TypeName *origtypname)
{
	HeapTuple	typeTup;
	PLpgSQL_type *typ;

	typeTup = SearchSysCache1(TYPEOID, ObjectIdGetDatum(typeOid));
	if (!HeapTupleIsValid(typeTup))
		elog(ERROR, "cache lookup failed for type %u", typeOid);

	typ = build_datatype(typeTup, typmod, collation, origtypname);

	ReleaseSysCache(typeTup);

	return typ;
}

本例中oid

postgres=# select * from pg_type where oid = 1790;
-[ RECORD 1 ]--+----------
oid            | 1790
typname        | refcursor
typnamespace   | 11
typowner       | 10
typlen         | -1
typbyval       | f
typtype        | b
typcategory    | U
typispreferred | f
typisdefined   | t
typdelim       | ,
typrelid       | 0
typsubscript   | -
typelem        | 0
typarray       | 2201
typinput       | textin
typoutput      | textout
typreceive     | textrecv
typsend        | textsend

$18 = {typname = 0x381a660 "refcursor", 
	typoid = 1790, 
	ttype = PLPGSQL_TTYPE_SCALAR, 
	typlen = -1, 
	typbyval = false, 
	typtype = 98 'b', 
	collation = 0, 
	typisarray = false, 
	atttypmod = -1, 
	origtypname = 0x0, 
	tcache = 0x0, 
	tupdesc_id = 0}

plpgsql_build_variable

拼一个PLpgSQL_variable记录到plpgsql_Datums，然后返回

PLpgSQL_variable *
plpgsql_build_variable(const char *refname, int lineno, PLpgSQL_type *dtype,
					   bool add2namespace)
{
	PLpgSQL_variable *result;

	switch (dtype->ttype)
	{
		case PLPGSQL_TTYPE_SCALAR:
			{
				/* Ordinary scalar datatype */
				PLpgSQL_var *var;

				var = palloc0(sizeof(PLpgSQL_var));
				var->dtype = PLPGSQL_DTYPE_VAR;
				var->refname = pstrdup(refname);
				var->lineno = lineno;
				var->datatype = dtype;
				/* other fields are left as 0, might be changed by caller */

				/* preset to NULL */
				var->value = 0;
				var->isnull = true;
				var->freeval = false;

				plpgsql_adddatum((PLpgSQL_datum *) var);
/*
变量是一定要加到plpgsql_Datums中的：

(gdb) p plpgsql_nDatums
$19 = 3

(gdb) p *((PLpgSQL_var*)plpgsql_Datums[2])
$21 = {dtype = PLPGSQL_DTYPE_VAR, dno = 2, 
	refname = 0x381b458 "cur", lineno = 3, isconst = false, 
	notnull = false, default_val = 0x0, datatype = 0x381b368, 
	cursor_explicit_expr = 0x0, cursor_explicit_argrow = 0, 
	cursor_options = 0, value = 0, isnull = true, 
    freeval = false, promise = PLPGSQL_PROMISE_NONE}
*/
				if (add2namespace)
					plpgsql_ns_additem(PLPGSQL_NSTYPE_VAR,
									   var->dno,
									   refname);
/*
变量也需要增加到ns_top中
2: *ns_top = {itemtype = PLPGSQL_NSTYPE_VAR, itemno = 2, prev = 0x381ad50, name = 0x381b488 "cur"}
*/
				result = (PLpgSQL_variable *) var;
				break;
			}
		case PLPGSQL_TTYPE_REC:
			{
				/* Composite type -- build a record variable */
				PLpgSQL_rec *rec;

				rec = plpgsql_build_record(refname, lineno,
										   dtype, dtype->typoid,
										   add2namespace);
				result = (PLpgSQL_variable *) rec;
				break;
			}
		case PLPGSQL_TTYPE_PSEUDO:
			ereport(ERROR,
					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
					 errmsg("variable \"%s\" has pseudo-type %s",
							refname, format_type_be(dtype->typoid))));
			result = NULL;		/* keep compiler quiet */
			break;
		default:
			elog(ERROR, "unrecognized ttype: %d", dtype->ttype);
			result = NULL;		/* keep compiler quiet */
			break;
	}

	return result;
}

注意union中复合变量的使用

注意在使用yacc的union内的结构体时，要注意格式，例如

%union {
		core_YYSTYPE			core_yystype;
	    ...
		struct varname
		{
			char *name;
			int  lineno;
		}						varname;
		...
}

%type <varname> decl_varname

// 在构造decl_varname时，返回值注意要使用结构体内的变量名
decl_varname	: T_WORD
						$$.name = $1.ident;
						$$.lineno = plpgsql_location_to_lineno(@1);
						...


// 在作为变量使用时，也要注意使用结构体内的变量名
decl_cursor_name:
		K_CURSOR decl_varname opt_scrollable
			{
				plpgsql_ns_push($2.name, PLPGSQL_LABEL_OTHER);
				$$.name = $2;
				$$.scrollopt = $3;
			}
			...

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2022-07-01，如有侵权请联系 cloudcommunity@tencent.com 删除

sql

css

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

sql

css

登录后参与评论

0 条评论

热度