Postgresql源码（46）plpgsql中的变量类型及对应关系

Postgresql源码（46）plpgsql中的变量类型及对应关系

相关
《Postgresql源码（41）plpgsql函数编译执行流程分析》
《Postgresql源码（46）plpgsql中的变量类型及对应关系》
《Postgresql源码（49）plpgsql函数编译执行流程分析总结》
《Postgresql源码（53）plpgsql语法解析关键流程、函数分析》
《Postgresql源码（112）plpgsql执行sql时变量何时替换为值》

plpgsql中的变量类型及对应关系

总结

1、PLpgSQL_datum.dtype共有5中类型，其中2中类型属于通用类型，覆盖pg_type中所有类型：由plpgsql_build_variable函数根据pg_type中查到的类型决定（对应关系见下表中的PLPGSQL_DTYPE_VAR、PLPGSQL_DTYPE_REC）

2、后3种类型单独应用于3种特殊场景（类行数据、列数据、触发器数据）

PLpgSQL_datum.dtype对应关系总结

【类型一、二】PLPGSQL_DTYPE_VAR、PLPGSQL_DTYPE_REC
(plpgsql_build_variable只能构造PLPGSQL_DTYPE_VAR、PLPGSQL_DTYPE_REC两种类型)PLpgSQL_datum.dtype   <  pe   <   系统表pg_type的typtype列
----------------------<-----------------------<------------------------
PLPGSQL_DTYPE_VAR     <  PLPGSQL_TTYPE_SCALAR <   TYPTYPE_BASE          : b for a base type|   TYPTYPE_ENUM          : e for an enum type|   TYPTYPE_RANGE         : r for a range type|   TYPTYPE_MULTIRANGE    : m for a multirange type|   TYPTYPE_DOMAIN        : d for a domain
PLPGSQL_DTYPE_REC     <  PLPGSQL_TTYPE_REC    <   TYPTYPE_COMPOSITE     : c for a composite type|   TYPTYPE_DOMAIN        : d for a domain|   TYPTYPE_PSEUDO        : p for a pseudo-type
null                  <  PLPGSQL_TTYPE_PSEUDO <   TYPTYPE_PSEUDO        : p for a pseudo-type（下面三种类型不在plpgsql_build_variable中构造）
【类型三】PLPGSQL_DTYPE_ROW
(PLpgSQL_row)场景一：游标的arguments例如：curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;场景二：超过一个out参数时，把所有的out参数拼成一个row加入datum中场景三：for loop的targetlist场景四：into多个变量【类型四】PLPGSQL_DTYPE_RECFIELD场景一：用于record类型中的某一列
【类型五】PLPGSQL_DTYPE_PROMISE场景一：trigger会默认创建一些变量用于记录触发中间态，例如tg_name、tg_when、tg_level、tg_op等，类型都是PLPGSQL_DTYPE_PROMISE

梳理过程

例如这样的几个变量

DECLARErow1 tf1%ROWTYPE;row2 tf1%ROWTYPE;txt1 text;x1 int[];n1 int := 1;

我们看到的变量类型是tf1%ROWTYPE、text等，那么在内部编译执行中，类型会被归为几类PLpgSQL_datum_type

（编译过程参考这篇：）

编译过程会把所有的变量存入plpgsql_Datums数组，例如当前例子中dtype就是PLpgSQL_datum_type类型

	  新增的plpgsql_Datums、ns_topp *((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}
===p *ns_top$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"}

在实际处理中，会把所有的变量都归为几类，就是上面看到的dtype：

/** Datum array node types*/
typedef enum PLpgSQL_datum_type
{PLPGSQL_DTYPE_VAR,PLPGSQL_DTYPE_ROW,PLPGSQL_DTYPE_REC,PLPGSQL_DTYPE_RECFIELD,PLPGSQL_DTYPE_PROMISE
} PLpgSQL_datum_type;

那么什么样的类型会分配到什么样的TYPE？

从一个datum构造plpgsql_build_variable说起：

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);if (add2namespace)plpgsql_ns_additem(PLPGSQL_NSTYPE_VAR,var->dno,refname);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;
}

plpgsql_build_variable会使用当前变量类型的dtype字段决定该变量的dtype，有如下对应关系

PLpgSQL_datum.dtype   <  pe
----------------------<--------------------
(plpgsql_build_variable)
PLPGSQL_DTYPE_VAR     <  PLPGSQL_TTYPE_SCALAR
PLPGSQL_DTYPE_REC     <  PLPGSQL_TTYPE_REC<  PLPGSQL_TTYPE_PSEUDO

类型的ttype在plpgsql_build_datatype函数中构造，有pg_type系统表中对应类型的typtype列的值决定

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;
}static PLpgSQL_type *
build_datatype(HeapTuple typeTup, int32 typmod,Oid collation, TypeName *origtypname)
{Form_pg_type typeStruct = (Form_pg_type) GETSTRUCT(typeTup);PLpgSQL_type *typ;if (!typeStruct->typisdefined)ereport(ERROR,(errcode(ERRCODE_UNDEFINED_OBJECT),errmsg("type "%s" is only a shell",NameStr(typeStruct->typname))));typ = (PLpgSQL_type *) palloc(sizeof(PLpgSQL_type));typ->typname = pstrdup(NameStr(typeStruct->typname));typ->typoid = typeStruct->oid;switch (typeStruct->typtype){case TYPTYPE_BASE:case TYPTYPE_ENUM:case TYPTYPE_RANGE:case TYPTYPE_MULTIRANGE:typ->ttype = PLPGSQL_TTYPE_SCALAR;break;case TYPTYPE_COMPOSITE:typ->ttype = PLPGSQL_TTYPE_REC;break;case TYPTYPE_DOMAIN:if (type_is_rowtype(typeStruct->typbasetype))typ->ttype = PLPGSQL_TTYPE_REC;elsetyp->ttype = PLPGSQL_TTYPE_SCALAR;break;case TYPTYPE_PSEUDO:if (typ->typoid == RECORDOID)typ->ttype = PLPGSQL_TTYPE_REC;elsetyp->ttype = PLPGSQL_TTYPE_PSEUDO;break;default:elog(ERROR, "unrecognized typtype: %d",(int) typeStruct->typtype);break;}typ->typlen = typeStruct->typlen;typ->typbyval = typeStruct->typbyval;typ->typtype = typeStruct->typtype;typ->collation = typeStruct->typcollation;if (OidIsValid(collation) && OidIsValid(typ->collation))typ->collation = collation;/* Detect if type is true array, or domain thereof *//* NB: this is only used to decide whether to apply expand_array */if (typeStruct->typtype == TYPTYPE_BASE){/** This test should include what get_element_type() checks.  We also* disallow non-toastable array types (i.e. oidvector and int2vector).*/typ->typisarray = (IsTrueArrayType(typeStruct) &&typeStruct->typstorage != TYPSTORAGE_PLAIN);}else if (typeStruct->typtype == TYPTYPE_DOMAIN){/* we can short-circuit looking up base types if it's not varlena */typ->typisarray = (typeStruct->typlen == -1 &&typeStruct->typstorage != TYPSTORAGE_PLAIN &&OidIsValid(get_base_element_type(typeStruct->typbasetype)));}elsetyp->typisarray = false;typ->atttypmod = typmod;/** If it's a named composite type (or domain over one), find the typcache* entry and record the current tupdesc ID, so we can detect changes* (including drops).  We don't currently support on-the-fly replacement* of non-composite types, else we might want to do this for them too.*/if (typ->ttype == PLPGSQL_TTYPE_REC && typ->typoid != RECORDOID){TypeCacheEntry *typentry;typentry = lookup_type_cache(typ->typoid,TYPECACHE_TUPDESC |TYPECACHE_DOMAIN_BASE_INFO);if (typentry->typtype == TYPTYPE_DOMAIN)typentry = lookup_type_cache(typentry->domainBaseType,TYPECACHE_TUPDESC);if (typentry->tupDesc == NULL)ereport(ERROR,(errcode(ERRCODE_WRONG_OBJECT_TYPE),errmsg("type %s is not composite",format_type_be(typ->typoid))));typ->origtypname = origtypname;typ->tcache = typentry;typ->tupdesc_id = typentry->tupDesc_identifier;}else{typ->origtypname = NULL;typ->tcache = NULL;typ->tupdesc_id = 0;}return typ;
}

type举例

-- a base type
postgres=# select oid, typname, typtype, typcategory from pg_type where typtype='b';oid  |                typname                 | typtype | typcategory 
-------+----------------------------------------+---------+-------------16 | bool                                   | b       | B17 | bytea                                  | b       | U18 | char                                   | b       | S19 | name                                   | b       | S20 | int8                                   | b       | N-- e for an enum type
-- r for a range type
postgres=# select oid, typname, typtype, typcategory from pg_type where typtype='r';oid  |  typname  | typtype | typcategory 
------+-----------+---------+-------------3904 | int4range | r       | R3906 | numrange  | r       | R3908 | tsrange   | r       | R3910 | tstzrange | r       | R3912 | daterange | r       | R3926 | int8range | r       | R-- m for a multirange type
postgres=# select oid, typname, typtype, typcategory from pg_type where typtype='m';oid  |    typname     | typtype | typcategory 
------+----------------+---------+-------------4451 | int4multirange | m       | R4532 | nummultirange  | m       | R4533 | tsmultirange   | m       | R4534 | tstzmultirange | m       | R4535 | datemultirange | m       | R4536 | int8multirange | m       | R-- d for a domain
postgres=# select oid, typname, typtype, typcategory from pg_type where typtype='d';oid  |     typname     | typtype | typcategory 
-------+-----------------+---------+-------------13540 | cardinal_number | d       | N13543 | character_data  | d       | S13545 | sql_identifier  | d       | S13551 | time_stamp      | d       | D13553 | yes_or_no       | d       | S-- c for a composite type
postgres=# select oid, typname, typtype, typcategory from pg_type where typtype='c';oid  |                typname                | typtype | typcategory 
-------+---------------------------------------+---------+-------------71 | pg_type                               | c       | C75 | pg_attribute                          | c       | C81 | pg_proc                               | c       | C83 | pg_class                              | c       | C12001 | pg_attrdef                            | c       | C12003 | pg_constraint                         | c       | C

PLpgSQL_datum.dtype对应关系总结

PLpgSQL_datum.dtype   <  pe   <   Form_ptype(pg_type)
----------------------<-----------------------<------------------------
(plpgsql_build_variable只能构造PLPGSQL_DTYPE_VAR、PLPGSQL_DTYPE_REC两种类型)
PLPGSQL_DTYPE_VAR     <  PLPGSQL_TTYPE_SCALAR <   TYPTYPE_BASE          : b for a base type<   TYPTYPE_ENUM          : e for an enum type<   TYPTYPE_RANGE         : r for a range type<   TYPTYPE_MULTIRANGE    : m for a multirange type<   TYPTYPE_DOMAIN        : d for a domain
PLPGSQL_DTYPE_REC     <  PLPGSQL_TTYPE_REC    <   TYPTYPE_COMPOSITE     : c for a composite type<   TYPTYPE_DOMAIN<   TYPTYPE_PSEUDO        : p for a pseudo-type<  PLPGSQL_TTYPE_PSEUDO <   TYPTYPE_PSEUDO（下面三种类型不在plpgsql_build_variable中构造）
PLPGSQL_DTYPE_ROW
(PLpgSQL_row)场景一：游标的arguments例如：curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;场景二：超过一个out参数时，把所有的out参数拼成一个row加入datum中场景三：for loop的targetlist场景四：into多个变量PLPGSQL_DTYPE_RECFIELD场景一：用于record类型中的某一列
PLPGSQL_DTYPE_PROMISE场景一：trigger会默认创建一些变量用于记录触发中间态，例如tg_name、tg_when、tg_level、tg_op等，类型都是PLPGSQL_DTYPE_PROMISE