PostgreSQL如何返回动态列数行列变换json,jsonb,record,temptable,tablefunc,pivottable,crosstab

作者

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

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.