Postgresql中支持两种行类型,两种类型使用上有一些区别,本篇结合实例简单介绍。
Postgresql使用plpgsql定义函数时,支持定义行类型,即可以完全继承表的行定义:
row1 table1%ROWTYPE; row2 table2%ROWTYPE;
rowtype内部的字段名与表保持严格一致,且在定义后就完全继承表的字段名了。
plpgsql中支持另一种行类型:record,这种类型在定义时不必指定具体类型:
rec1 record; rec2 record;
在使用时,record的内部结构由赋值时指定,PG中给行类型赋值只有两种方式:
record的行结构与结果集保持一致,也就是按照 查询结果中的列名(或列名as 别名)来定义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 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
从实例结果来看有两点结论:
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
%ROWTYPE与RECORD不同点:
%ROWTYPE与RECORD相同点:
表结构: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->erh
简化用例
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
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}}
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基本相同。