相关 《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 < PLpgSQL_type.ttype < 系统表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
梳理过程
例如这样的几个变量
DECLARE
row1 tf1%ROWTYPE;
row2 tf1%ROWTYPE;
txt1 text;
x1 int[];
n1 int := 1;
我们看到的变量类型是tf1%ROWTYPE、text等，那么在内部编译执行中，类型会被归为几类PLpgSQL_datum_type
（编译过程参考这篇：https://blog.csdn.net/jackgo73/article/details/123923201）
编译过程会把所有的变量存入plpgsql_Datums数组，例如当前例子中dtype就是PLpgSQL_datum_type类型
新增的plpgsql_Datums、ns_top
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}
===
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 < PLpgSQL_type.ttype
----------------------<--------------------
(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;
else
typ->ttype = PLPGSQL_TTYPE_SCALAR;
break;
case TYPTYPE_PSEUDO:
if (typ->typoid == RECORDOID)
typ->ttype = PLPGSQL_TTYPE_REC;
else
typ->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)));
}
else
typ->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 | B
17 | bytea | b | U
18 | char | b | S
19 | name | b | S
20 | 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 | R
3906 | numrange | r | R
3908 | tsrange | r | R
3910 | tstzrange | r | R
3912 | daterange | r | R
3926 | 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 | R
4532 | nummultirange | m | R
4533 | tsmultirange | m | R
4534 | tstzmultirange | m | R
4535 | datemultirange | m | R
4536 | 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 | N
13543 | character_data | d | S
13545 | sql_identifier | d | S
13551 | time_stamp | d | D
13553 | 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 | C
75 | pg_attribute | c | C
81 | pg_proc | c | C
83 | pg_class | c | C
12001 | pg_attrdef | c | C
12003 | pg_constraint | c | C
PLpgSQL_datum.dtype对应关系总结
PLpgSQL_datum.dtype < PLpgSQL_type.ttype < Form_pg_type.typtype(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
发表评论