Postgresql中支持两种行类型，两种类型使用上有一些区别，本篇结合实例简单介绍。
1 实例分析
ROWTYPE
Postgresql使用plpgsql定义函数时，支持定义行类型，即可以完全继承表的行定义：
row1 table1%ROWTYPE; row2 table2%ROWTYPE;
rowtype内部的字段名与表保持严格一致，且在定义后就完全继承表的字段名了。
RECORD
plpgsql中支持另一种行类型：record，这种类型在定义时不必指定具体类型：
rec1 record; rec2 record;
在使用时，record的内部结构由赋值时指定，PG中给行类型赋值只有两种方式：
- SELECT into row1
- SELECT into rec1
- FOR row1 IN SELECT * FROM table1 WHERE c3 = 1;
- FOR rec1 IN SELECT * FROM table1 WHERE c3 = 1;
record的行结构与结果集保持一致，也就是按照 查询结果中的列名（或列名as 别名）来定义record具体的字段名。
ROWTYPE实例
从实例结果来看有两点结论：
- ROWTYPE变量内部字段名不会受结果集影响，与表字段保持一致
- ROWTYPE变量内部字段的赋值，是按结果集顺序赋值的，与结果集中的字段名无关
drop table tf1;
create table tf1(c1 int, c2 int);
insert into tf1 values(1,1000);
insert into tf1 values(2,2000);
insert into tf1 values(3,3000);
insert into tf1 values(4,4000);
insert into tf1 values(5,5000);
insert into tf1 values(6,6000);
CREATE OR REPLACE PROCEDURE tfun1() AS $$
DECLARE
row1 tf1%ROWTYPE;
row2 tf1%ROWTYPE;
row3 tf1%ROWTYPE;
BEGIN
SELECT * INTO row1 from tf1 where c1 > 1;
raise notice 'row1.c1: %', row1.c1;
raise notice 'row1.c2: %', row1.c2;
SELECT 2000,c1 INTO row2 from tf1 where c1 = 1;
raise notice 'row2.c1: %', row2.c1;
raise notice 'row2.c2: %', row2.c2;
SELECT c2 as xxx INTO row3 from tf1 where c1 = 1;
raise notice 'row3.c1: %', row3.c1;
raise notice 'row3.c2: %', row3.c2;
END;
$$ LANGUAGE plpgsql;
postgres=# call tfun1();
NOTICE: row1.c1: 2
NOTICE: row1.c2: 2000
NOTICE: row2.c1: 2000
NOTICE: row2.c2: 1
NOTICE: row3.c1: 1000
NOTICE: row3.c2: <NULL>
CALLRECORD实例
从实例结果来看有两点结论：
- RECORD变量内部字段名会受结果集影响，字段名与结果集保持一致
- RECORD变量内部字段的赋值，也是按结果集顺序赋值的
drop table tf1;
create table tf1(c1 int, c2 int);
insert into tf1 values(1,1000);
insert into tf1 values(2,2000);
insert into tf1 values(3,3000);
insert into tf1 values(4,4000);
insert into tf1 values(5,5000);
insert into tf1 values(6,6000);
CREATE OR REPLACE PROCEDURE tfun2() AS $$
DECLARE
row1 record;
row2 record;
row3 record;
BEGIN
SELECT * INTO row1 from tf1 where c1 > 1;
raise notice 'row1.c1: %', row1.c1;
raise notice 'row1.c2: %', row1.c2;
SELECT 2000,c1 INTO row2 from tf1 where c1 = 1;
raise notice 'row2.c1: %', row2.c1;
-- raise notice 'row2.c2: %', row2.c2;
SELECT c2 INTO row3 from tf1 where c1 = 1;
-- raise notice 'row3.c1: %', row3.c1;
raise notice 'row3.c2: %', row3.c2;
SELECT c2 as xxx INTO row3 from tf1 where c1 = 1;
raise notice 'row3.xxx: %', row3.xxx;
END;
$$ LANGUAGE plpgsql;
postgres=# call tfun2();
NOTICE: row1.c1: 2
NOTICE: row1.c2: 2000
NOTICE: row2.c1: 1
NOTICE: row3.c2: 1000
NOTICE: row3.xxx: 1000
CALL2 内部代码分析
2.0 总结
%ROWTYPE与RECORD不同点：
- ROWTYPE的类型就是表的OID，RECORD的类型是统一的record（oid=2249）。
- ROWTYPE用的表的tupledesc，RECORD用 的是SPI返回值的desc：SPI_tuptable->tupdesc
%ROWTYPE与RECORD相同点：
- 都是用PLpgSQL_rec的expand record保存行数据
- 都是用PLpgSQL_recfield记录字段名 和 字段对应数据在expand record中的位置
2.1 ROWTYPE
表结构：create table tf1(c1 int, c2 int);
row1 tf1%ROWTYPE;：编译后
PLpgSQL_rec =
{ dtype = PLPGSQL_DTYPE_REC,
dno = 1,
refname = 0x2b37e08 "row1",
lineno = 3,
isconst = false,
notnull = false,
default_val = 0x0,
datatype = 0x2b37d50, --> {typname = 0x2b37da0 "tf1", typoid = 16637, ttype = PLPGSQL_TTYPE_REC }
rectypeid = 16637, -->【表的OID】
firstfield = 5, --> 【指向dno=5的recfield】
erh = 0x0}
PLpgSQL_recfield =
{ dtype = PLPGSQL_DTYPE_RECFIELD,
dno = 4, <<<---------------------------------------------------------
fieldname = "c1",
recparentno = 1, |
nextfield = -1, |
rectupledescid = 13, |
finfo = {fnumber = 1, ftypeid = 23, ftypmod = -1, fcollation = 0} |
} |
|
PLpgSQL_recfield = |
{ dtype = PLPGSQL_DTYPE_RECFIELD, |
dno = 5, |
fieldname = "c2", |
recparentno = 1, /
nextfield = 4, -----------------------------------------------------
rectupledescid = 13,
finfo = {fnumber = 2, ftypeid = 23, ftypmod = -1, fcollation = 0}}SELECT * INTO row1 from tf1 where c1 > 1;如何赋值？
exec_stmt_execsql
exec_prepare_plan 【1】删掉into做计划：SELECT * from tf1 where c1 > 1
SPI_execute_plan_with_paramlist 【2】执行：SELECT * from tf1 where c1 > 1
if (stmt->into)
SPITupleTable *tuptab = SPI_tuptable; 【3】全局变量拿到结果元组
target = (PLpgSQL_variable *) estate->datums[stmt->target->dno]; 【4】找到PLpgSQL_rec
exec_move_row(estate, target, tuptab->vals[0], tuptab->tupdesc); 【5】把第一行赋值给PLpgSQL_rec
| newerh = make_expanded_record_for_rec(estate, rec, tupdesc, NULL) 【6】用tupdesc组装扩展元组
| make_expanded_record_from_typeid
| 【7】申请内存结构
| |----------------------|
| | ExpandedRecordHeader |
| | Datum | <----erh->dvalues
| | Datum |
| | bool | <----erh->dnulls
| | bool |
| 【8】给ERH填充变量，记录类型信息
| expanded_record_set_tuple
| 【9】给变量赋值
| newtuple = heap_copytuple(tuple)
| 【9】记录flat格式元组
| erh->fvalue = newtuple;
| 【10】记录expand需要的数据
| erh->fstartpt = (char *) newtuple->t_data;
| assign_record_var 【11】把新的erh赋值给PLpgSQL_rec->erh2.2 RECORD
简化用例
代码语言：javascript复制drop table tf1;
create table tf1(c1 int, c2 int);
insert into tf1 values(1,1000);
insert into tf1 values(2,2000);
insert into tf1 values(3,3000);
insert into tf1 values(4,4000);
insert into tf1 values(5,5000);
insert into tf1 values(6,6000);
CREATE OR REPLACE PROCEDURE tfun2() AS $$
DECLARE
row1 record;
BEGIN
SELECT c1, c2 as xxx INTO row1 from tf1 where c1 = 1;
raise notice 'row1.c1: %', row1.c1;
raise notice 'row1.xxx: %', row1.xxx;
END;
$$ LANGUAGE plpgsql;
postgres=# call tfun2();
NOTICE: row1.c1: 1
NOTICE: row1.xxx: 1000
CALLrow1 record;：编译后
PLpgSQL_rec = {
dtype = PLPGSQL_DTYPE_REC,
dno = 1,
refname = 0x2b6e0c8 "row1",
lineno = 3,
isconst = false,
notnull = false,
default_val = 0x0,
datatype = 0x2b6e010, ----> {typname = 0x2b6e060 "record", typoid = 2249, ttype = PLPGSQL_TTYPE_REC}
rectypeid = 2249,
firstfield = 3,
erh = 0x0}
PLpgSQL_recfield = {
dtype = PLPGSQL_DTYPE_RECFIELD,
dno = 2,
fieldname = 0x2b6e510 "c1",
recparentno = 1,
nextfield = -1,
rectupledescid = 32,
finfo = {fnumber = 1, ftypeid = 23, ftypmod = -1, fcollation = 0}}
PLpgSQL_recfield = {
dtype = PLPGSQL_DTYPE_RECFIELD,
dno = 3,
fieldname = 0x2b754b8 "xxx",
recparentno = 1,
nextfield = 2,
rectupledescid = 32,
finfo = {fnumber = 2, ftypeid = 23, ftypmod = -1, fcollation = 0}}SELECT c1, c2 as xxx INTO row1 from tf1 where c1 = 1;如何赋值？
exec_stmt_execsql
exec_prepare_plan 【1】删掉into做计划：SELECT c1, c2 as xxx from tf1 where c1 = 1
SPI_execute_plan_with_paramlist 【2】执行：SELECT c1, c2 as xxx from tf1 where c1 = 1
if (stmt->into)
exec_move_row与ROWTYPE基本相同。
