Postgresql RECORD与%ROWTYPE类型

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>
CALL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

RECORD实例

从实例结果来看有两点结论：

• 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
CALL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

2 内部代码分析

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}}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

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->erh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

2.2 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;
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
CALL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

row1 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}}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

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
1
2
3
4
5

与ROWTYPE基本相同。