PostgreSQL: Get values of a register as multiple rows

Question

Using PostgreSQL 9.3, I'm creating a Jasper reports template to make a pdf report. I want to create reports of different tables, with multiple columns, all with the same template. A solution could be to get values of register as pairs of column name and value per id.

By example, if I had a table like:

id | Column1          | Column2     | Column3
-------------------------------------------------
1  | Register1C1      | Register1C2 | Register1C3

I would like to get the register as:

Id | ColumnName | Value
-----------------------------
1  | Column1    | Register1C1
1  | Column2    | Register1C2
1  | Column3    | Register1C3

The data type of value columns can vary!
Is it possible? How can I do this?

How many columns do you have ?? – Vivek S. Dec 03 '15 at 05:21 — Vivek S., Dec 03 '15 at 05:21
That's the issue. It must work with 'n' columns. – Leonardo De La Cruz Dec 03 '15 at 05:30 — Leonardo De La Cruz, Dec 03 '15 at 05:30
and what is the Datatype of all columns ??? – Vivek S. Dec 03 '15 at 05:35 — Vivek S., Dec 03 '15 at 05:35
Obviously, your version of Postgres, please. – Erwin Brandstetter Dec 03 '15 at 05:41 — Erwin Brandstetter, Dec 03 '15 at 05:41
The datatype is variable too. I'm using Postgres 9.3. – Leonardo De La Cruz Dec 03 '15 at 06:23 — Leonardo De La Cruz, Dec 03 '15 at 06:23

Vivek S. · Answer 1 · 2015-12-03T06:18:13.433

SELECT id
    ,unnest(string_to_array('col1,col2,col3', ',')) col_name
    ,unnest(string_to_array(col1 || ',' || col2 || ',' || col3, ',')) val
FROM t

Try following method:

My sample table name is t,to get the n columns name you can use this query

select string_agg(column_name,',') cols from information_schema.columns where
table_name='t' and column_name<>'id'

this query will selects all columns in your table except id column.If you want to specify schema name then use table_schema='your_schema_name' in where clause

To create select query dynamically

SELECT 'select id,unnest(string_to_array(''' || cols || ''','','')) col_name,unnest(string_to_array(' || cols1 || ','','')) val from t'
FROM (
        SELECT string_agg(column_name, ',') cols  -- here we'll get all the columns in table t
            ,string_agg(column_name, '||'',''||') cols1 
        FROM information_schema.columns
        WHERE table_name = 't'
            AND column_name <> 'id'
) tb;

And using following plpgsql function dynamically creates SELECT id,unnest(string_to_array('....')) col_name,unnest(string_to_array(.., ',')) val FROM t and execute.

    CREATE OR replace FUNCTION fn ()
RETURNS TABLE (
        id INT
        ,columname TEXT
        ,columnvalues TEXT
        ) AS $$

DECLARE qry TEXT;
BEGIN
    SELECT 'select id,unnest(string_to_array(''' || cols || ''','','')) col_name,unnest(string_to_array(' || cols1 || ','','')) val from t'
    INTO qry
    FROM (
        SELECT string_agg(column_name, ',') cols
            ,string_agg(column_name, '||'',''||') cols1
        FROM information_schema.columns
        WHERE table_name = 't'
            AND column_name <> 'id'
        ) tb;

    RETURN QUERY
    EXECUTE format(qry);
END;$$
LANGUAGE plpgsql

Call this function like select * from fn()

I think using `unnest(array[col1,col2,col3])` instead of unnesting a an array that was created from a comma separated string) would be more robust (no problem if `col` contains a `,` and it would also preserve the datatypes of the columns. — , Dec 03 '15 at 07:27

score 0 · Accepted Answer · edited May 23 '17 at 12:25

If all your columns share the same data type and order of rows does not have to be enforced:

SELECT t.id, v.*
FROM   tbl t, LATERAL (
   VALUES
     ('col1', col1)
   , ('col2', col2)
   , ('col3', col3)
      -- etc.
   ) v(col, val);

About LATERAL (requires Postgres 9.3 or later):

What is the difference between LATERAL and a subquery in PostgreSQL?

Combining it with a VALUES expression:

For varying data types, the common denominator would be text, since every type can be cast to text. Plus, order enforced:

SELECT t.id, v.col, v.val
FROM   tbl t, LATERAL (
   VALUES
     (1, 'col1', col1::text)
   , (2, 'col2', col2::text)
   , (3, 'col3', col3::text)
     -- etc.
   ) v(rank, col, val)
ORDER  BY t.id, v.rank;

In Postgres 9.4 or later use the new unnest() for multiple arrays:

SELECT t.id, v.*
FROM   tbl t, unnest('{col1,col2,col3}'::text[]
               , ARRAY[col1,col2,col3]) v(col, val);
           --  , ARRAY[col1::text,col2::text,col3::text]) v(col, val);

The commented alternative for varying data types.

Full automation for Postgres 9.4:

The query above is convenient to automate for a dynamic set of columns:

CREATE OR REPLACE FUNCTION f_transpose (_tbl regclass, VARIADIC _cols text[])
  RETURNS TABLE (id int, col text, val text) AS
$func$
BEGIN
   RETURN QUERY EXECUTE format(
     'SELECT t.id, v.* FROM %s t, unnest($1, ARRAY[%s]) v'
   , _tbl, array_to_string(_cols, '::text,') || '::text'))
-- , _tbl, array_to_string(_cols, ',')))  -- simple alternative for only text
   USING _cols;
END
$func$  LANGUAGE plpgsql;

Call - with table name and any number of column names, any data types:

SELECT * FROM f_transpose('table_name', 'column1', 'column2', 'column3');

Weakness: the list of column names is not safe against SQL injection. You could gather column names from pg_attribute instead. Example:

How to perform the same aggregation on every column, without listing the columns?

Great! It worked! For my Jasper report I can pass the table name and columns name by parameters and use them as: SELECT t.gid, v.col, v.val FROM tt.$P!{nombreCapa} t, LATERAL ( VALUES $P!{columnsParameter} ) v(rank, col, val) ORDER BY t.gid, v.rank; — Leonardo De La Cruz, Dec 03 '15 at 06:55

PostgreSQL: Get values of a register as multiple rows

2 Answers2

Full automation for Postgres 9.4: