How PostgreSQL Returns a Dynamic Number of Columns: Row-Column Convert

By digoal

Background

You may want an SQL statement to return a dynamic number of columns in some scenarios, such as a BI report system. The number of columns may be variable in the results of a query.

For example, it returns the results below:

id,arr  
1,{1,2}  
2,{1,2,3,4}

Alternatively, it returns other, more complicated results.

There are many specific methods, including:

1. You can change the type of N and convert it to N columns using the tablefunc plug-in to convert rows and columns.

2. The example mentioned at the beginning of the article can be encapsulated in a function. The returned temporary table is defined according to the length of arrays in the function, and finally, it returns results by querying the temporary table. For example:

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;

You cannot use CTE to query this new temporary table created in the function:

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;  
                                                                 ^

It is divided into two steps.

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. If the BI system supports only one-time calls, you can consider making the return value in JSON format. No matter how many columns it returns, they can be included in a JSON.

The result is encapsulated in the JSON. Then, parse the JSON with jsonb_populate_recordset or json_populate_recordset:

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. If a query can define the return structure, functions that use the record return type can also realize dynamic columns:

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)

Community

How PostgreSQL Returns a Dynamic Number of Columns: Row-Column Convert

Background

References

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

PolarDB for PostgreSQL

Serverless Workflow

AnalyticDB for PostgreSQL

ApsaraDB RDS for SQL Server