digoal
2020-12-26
PostgreSQL , 动态列数 , json , jsonb , record , temp table , tablefunc , pivot table , crosstab , array
在某些场景中, 可能希望sql返回动态列数, 例如BI报表系统. 一个查询的结果中可能包含的列数不定.
例如返回:
id,arr
1,{1,2}
2,{1,2,3,4}
又或者是其他更复杂的情况.
具体的方法比较多, 包括:
1、tablefunc插件, 行列变换, 里面支持自定义N type, 变换为N列.
《PostgreSQL Oracle 兼容性 - 行列变换(pivot unpivot) (tablefunc, crosstab)》
2、开篇的例子, 可以封装在函数中, 在函数内根据array长度定义返回的临时表, 最后通过查询这个临时表来返回. 例子
create or replace function dyn_out (int4[]) returns void as $$
declare
sql1 text := '';
sql2 text := '';
i int := 1;
x int;
begin
sql1 := 'create temp table if not exists temp1 (';
sql2 := 'insert into temp1 values (';
foreach x in array $1 loop
sql1 := sql1||'c'||i||' int ,';
sql2 := sql2||x||',';
i := i+1;
end loop;
sql1 := rtrim(sql1,',')||') on commit drop';
sql2 := rtrim(sql2,',')||')';
execute sql1;
execute sql2;
end;
$$ language plpgsql strict;
不支持采用CTE查询这个在函数中新建的临时表.
postgres=# with a as (select dyn_out(array[1,2,3,4])) select * from temp1;
ERROR: relation "temp1" does not exist
LINE 1: ...h a as (select dyn_out(array[1,2,3,4])) select * from temp1;
^
必须分为两次
postgres=# begin;
BEGIN
postgres=*# select dyn_out(array[1,2,3,4]);
dyn_out
---------
(1 row)
postgres=*# select * from temp1;
c1 | c2 | c3 | c4
----+----+----+----
1 | 2 | 3 | 4
(1 row)
postgres=*# end;
COMMIT
3、如果BI系统仅支持一次调用, 可以考虑使用JSON类型作为返回值, 不管多少列, 都可以包含在一个JSON里面.
将结果封装在json内, 通过jsonb_populate_recordset或json_populate_recordset解析json:
postgres=# create type ints_4 as (a int, b int, c int, d int);
CREATE TYPE
postgres=# select * from jsonb_populate_recordset(null::ints_4, '[{"a":1,"b":2}, {"a":3,"b":4,"c":100,"d":200}]');
a | b | c | d
---+---+-----+-----
1 | 2 | |
3 | 4 | 100 | 200
(2 rows)
4、如果query能定义返回结构, 采用record返回类型的函数也能实现动态列.
create or replace function f(text) returns setof record as $$
declare
begin
return query execute $1;
end;
$$ language plpgsql strict;
select * from f($$select generate_series(1,10), random()*10, md5(random()::text), now()$$)
as t (id int, c1 float8, c2 text, c3 timestamptz);
id | c1 | c2 | c3
----+--------------------+----------------------------------+-------------------------------
1 | 1.2760291454197414 | 0f108bfc50b2a9b988128dd6c8ea4d9e | 2020-12-26 12:26:09.178573+08
2 | 9.820227323439639 | 551740c9ca1fe1db782f8695d8b4272e | 2020-12-26 12:26:09.178573+08
3 | 7.771695476696081 | bf284c1631865e58fd2ee23f2a2cb354 | 2020-12-26 12:26:09.178573+08
4 | 5.25456496894833 | 6373ade2ba1421eabfea89c42c0ce339 | 2020-12-26 12:26:09.178573+08
5 | 9.606696936766994 | 6a6fe86d124066425b5257093f4f0d86 | 2020-12-26 12:26:09.178573+08
6 | 2.7529741829887655 | e3ba7b4ac3b3d021013b617428f64d26 | 2020-12-26 12:26:09.178573+08
7 | 3.508055632020657 | 4c95a661968ee0bbf6248e4739d2183f | 2020-12-26 12:26:09.178573+08
8 | 3.2396315515742913 | f5602c9dffe52b58917ea73be30eb0a5 | 2020-12-26 12:26:09.178573+08
9 | 0.7574322479838003 | 58be0d9d0a5f1b18ecedf38303932885 | 2020-12-26 12:26:09.178573+08
10 | 7.912392299341349 | 2aab9f549d39d94e909189fb4a5ee62b | 2020-12-26 12:26:09.178573+08
(10 rows)
《PostgreSQL Oracle 兼容性 - 行列变换(pivot unpivot) (tablefunc, crosstab)》
https://www.postgresql.org/docs/13/functions-json.html#FUNCTIONS-JSON-PROCESSING
https://www.postgresql.org/docs/13/tablefunc.html
https://www.postgresql.org/docs/13/plpgsql.html
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
