作者
digoal
日期
2020-11-05
标签
PostgreSQL , inline code , table , function , rowtype
背景
在代码中输入动态表名, 取其rowtype, 目前存在bug.
1、会检查动态表名变量, 变量名必须是已有表名, 说明不支持动态表名.
例如
v text := 'tblname';
x v%rowtype;
v必须是个已经存在的表或type. 否则会编译check出错.
2、检查通过后, 实际执行时, 变量会替换成value的表名, 说明支持动态表名
v text := 'tblname';
x v%rowtype;
实际上x对应的是tblname的结构, 而不是v的结构.
功能上自相矛盾.
复现过程如下:
```
postgres=> \d b
Table "public.b"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
c1 | jsonb | | |
Indexes:
"idx_b_1" gin (c1)
postgres=> \d a
Table "public.a"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | bigint | | not null |
gid | integer | | |
score | integer | | |
info | text | | |
Indexes:
"a_pkey" PRIMARY KEY, btree (id)
"idx_a_1" btree (gid, score)
"idx_a_2" btree (gid)
postgres=> \d tbl
Table "public.tbl"
Column | Type | Collation | Nullable | Default
----------+-----------------------------+-----------+----------+---------
col | integer | | |
c1 | integer | | |
c2 | integer | | |
mod_time | timestamp without time zone | | |
postgres=> select * from a limit 1;
id | gid | score | info
----+--------+-------+----------------------------------
1 | 112736 | 393 | 3d41b33b5e739b30eebfa15109e2db9f
(1 row)
postgres=> select * from tbl limit 1;
col | c1 | c2 | mod_time
------+----+----+----------------------------
9150 | 32 | 47 | 2020-10-31 17:06:28.452212
(1 row)
postgres=> do language plpgsql $$
declare
y text := 'tbl';
b y%rowtype;
begin
select tbl.* into b from tbl limit 1;
raise notice '%', b;
end;
$$;
ERROR: relation "y" does not exist
CONTEXT: compilation of PL/pgSQL function "inline_code_block" near line 4
postgres=> do language plpgsql $$
declare
a text := 'tbl';
b a%rowtype;
begin
select tbl.* into b from tbl limit 1;
raise notice '%', b;
end;
$$;
NOTICE: (9150,32,47,"2020-10-31 17:06:28.452212")
DO
postgres=> create or replace function f(a text) returns void as $$
declare v a%rowtype;
begin
execute format('select * from %I limit 1', a) into v;
raise notice '%: %', a, v;
end;
$$ language plpgsql strict;
CREATE FUNCTION
postgres=> select * from f('tbl');
NOTICE: tbl: (9150,32,47,"2020-10-31 17:06:28.452212")
f
(1 row)
postgres=> select * from f('b');
ERROR: invalid input syntax for type integer: "[{"a": 1, "b": 2}, {"c": 2, "d": 4}]"
CONTEXT: PL/pgSQL function f(text) line 4 at EXECUTE
```
已上报bug.
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
