Postgres dynamic column headers (from another table)

Question

Just a silly example:

Table A:

1. eggs
2. bread
3. cheese

Table B (when they are eaten):

1. Egg | date
2. Bread | date
3. Egg | date
4. cheese | date
5. Bread | date

For statistics purpouses, i need to have statistics per date per food type in a look like this:

Table Statistics:

             egg   |   bread   |   cheese

date1         2          1            0    

date2         6          4            2

date3         2          0            0

I need the column headers to be dynamic in the report (if new ones are added, it should automatically appear).

Any idea how to make this in postgres?

Thanks.

Answer 1

based on answer Postgres dynamic column headers (from another table) (the work of Eric Vallabh Minikel) i improved the function to be more flexible and convenient. I think it might be useful for others too, especially as it only relies on pg/plsql and does not need installation of extentions as other derivations of erics work (i.e. plpython) do. Testet with 9.3.5 but should also work at least down to 9.2.

Improvements:

• deal with pivoted column names containing spaces
• deal with multiple row header columns
• deal with aggregate function in pivot cell as well as non-aggregated pivot cell (last parameter might be 'sum(cellval)' as well as 'cellval' in case the underlying table/view already does aggregation)
• auto detect data type of pivot cell (no need to pass it to the function any more)

Useage:

SELECT get_crosstab_statement('table_to_pivot', ARRAY['rowname' [, <other_row_header_columns_as_well>], 'colname', 'max(cellval)');

Code:

CREATE OR REPLACE FUNCTION get_crosstab_statement(tablename character varying, row_header_columns character varying[], pivot_headers_column character varying, pivot_values character varying)
  RETURNS character varying AS
$BODY$
--returns the sql statement to use for pivoting the table
--based on: http://www.cureffi.org/2013/03/19/automatically-creating-pivot-table-column-names-in-postgresql/
--based on: https://stackoverflow.com/questions/4104508/postgres-dynamic-column-headers-from-another-table
--based on: http://www.postgresonline.com/journal/categories/24-tablefunc

DECLARE
    arrayname CONSTANT character varying := 'r';

    row_headers_simple character varying;
    row_headers_quoted character varying;
    row_headers_castdown character varying;
    row_headers_castup character varying;
    row_header_count smallint;
    row_header record;

    pivot_values_columnname character varying;
    pivot_values_datatype character varying;
    pivot_headers_definition character varying;
    pivot_headers_simple character varying;

    sql_row_headers character varying;
    sql_pivot_headers character varying;
    sql_crosstab_result character varying;

BEGIN
    -- 1. create row header definitions
    row_headers_simple   :=         array_to_string(row_header_columns, ', ');
    row_headers_quoted   := '''' || array_to_string(row_header_columns, ''', ''') || '''';
    row_headers_castdown :=         array_to_string(row_header_columns, '::text, ') || '::text';

    row_header_count     := 0;
    sql_row_headers      := 'SELECT column_name, data_type
                            FROM information_schema.columns
                            WHERE table_name = ''' || tablename || ''' AND column_name IN (' || row_headers_quoted || ')';
    FOR row_header IN EXECUTE sql_row_headers LOOP
        row_header_count := row_header_count + 1;
        row_headers_castup := COALESCE(row_headers_castup || ', ', '') || arrayname || '[' || row_header_count || ']::' || row_header.data_type || ' AS ' || row_header.column_name;
    END LOOP;

    -- 2. retrieve basic column name in case an aggregate function is used
    SELECT coalesce(substring(pivot_values FROM '.*\((.*)\)'), pivot_values)
    INTO pivot_values_columnname;

    -- 3. retrieve pivot values datatype
    SELECT data_type
    FROM information_schema.columns
    WHERE table_name = tablename AND column_name = pivot_values_columnname
    INTO pivot_values_datatype;

    -- 4. retrieve list of pivot column names.
    sql_pivot_headers := 'SELECT string_agg(DISTINCT quote_ident(' || pivot_headers_column || '), '', '' ORDER BY quote_ident(' || pivot_headers_column || ')) as names, string_agg(DISTINCT quote_ident(' || pivot_headers_column || ') || '' ' || pivot_values_datatype || ''', '', '' ORDER BY quote_ident(' || pivot_headers_column || ') || '' ' || pivot_values_datatype || ''') as definitions FROM ' || tablename || ';';
    EXECUTE sql_pivot_headers INTO pivot_headers_simple, pivot_headers_definition;

    -- 5. set up the crosstab query
    sql_crosstab_result := 'SELECT  ' || replace (row_headers_castup || ', ' || pivot_headers_simple, ', ', ',
        ') || '
FROM    crosstab (
        ''SELECT ARRAY[' || row_headers_castdown || '] AS ' || arrayname || ', ' || pivot_headers_column || ', ' || pivot_values || '
        FROM ' || tablename || '
        GROUP BY ' || row_headers_simple || ', ' || pivot_headers_column || (CASE pivot_values_columnname=pivot_values WHEN true THEN ', ' || pivot_values ELSE '' END) || '
        ORDER BY ' || row_headers_simple || '''
    ,
        ''SELECT DISTINCT ' || pivot_headers_column || '
        FROM ' || tablename || '
        ORDER BY ' || pivot_headers_column || '''
    ) AS newtable (
        ' || arrayname || ' varchar[]' || ',
        ' || replace(pivot_headers_definition, ', ', ',
        ') || '
    );';

    RETURN sql_crosstab_result;
 END

 $BODY$
  LANGUAGE plpgsql STABLE
  COST 100;

Answer 2

The code is working fine. you will receive output as dynamic query.

CREATE OR REPLACE FUNCTION public.pivotcode(tablename character varying, rowc character varying, colc character varying, cellc character varying, celldatatype character varying)

RETURNS character varying AS

$BODY$

declare

    dynsql1 varchar;
    dynsql2 varchar;
    columnlist varchar;

begin

    -- 1. retrieve list of column names.

    dynsql1 = 'select string_agg(distinct ''_''||'||colc||'||'' '||celldatatype||''','','' order by ''_''||'||colc||'||'' '||celldatatype||''') from '||tablename||';';
    execute dynsql1 into columnlist;

    -- 2. set up the crosstab query

    --create temp table temp as

    dynsql2 = 'select * from crosstab ( ''select '||rowc||','||colc||','||cellc||' from '||tablename||' group by 1,2 order by 1,2'', ''select distinct '||colc||' from '||tablename||' order by 1'' ) as newtable ( '||rowc||' varchar,'||columnlist||' );';

    return dynsql2;

 end

 $BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

Answer 3

I came across the same problem, and found an alternative solution. I'd like to ask for comments here.

The idea is to create the "output type" string for crosstab dynamically. The end result can't be returned by a plpgsql function, because that function would either need to have a static return type (which we don't have) or return setof record, thus having no advantage over the original crosstab function. Therefore my function saves the output cross-table in a view. Likewise, the input table of "pivot" data not yet in cross-table format is taken from an existing view or table.

The usage would be like this, using your example (I added the "fat" field to illustrate the sorting feature):

CREATE TABLE food_fat (name character varying(20) NOT NULL, fat integer);
CREATE TABLE eaten (food character varying(20) NOT NULL, day date NOT NULL);
-- This view will be formatted as cross-table.
-- ORDER BY is important, otherwise crosstab won't merge rows
CREATE TEMPORARY VIEW mymeals AS 
       SELECT day,food,COUNT(*) AS meals FROM eaten 
       GROUP BY day, food ORDER BY day;
SELECT auto_crosstab_ordered('mymeals_cross',
      'mymeals', 'day', 'food', 'meals',  -- what table to convert to cross-table
      'food_fat', 'name', 'fat');         -- where to take the columns from

The last statement creates a view mymeals_cross that looks like this:

SELECT * FROM mymeals_cross;
    day     | bread | cheese | eggs 
------------+-------+--------+------
 2012-06-01 |     3 |      3 |    2
 2012-06-02 |     2 |      1 |    3
(2 rows)

Here comes my implementation:

-- FUNCTION get_col_type(tab, col)
-- returns the data type of column <col> in table <tab> as string
DROP FUNCTION get_col_type(TEXT, TEXT);
CREATE FUNCTION get_col_type(tab TEXT, col TEXT, OUT ret TEXT)
AS $BODY$ BEGIN
EXECUTE $f$
   SELECT atttypid::regtype::text
   FROM pg_catalog.pg_attribute
   WHERE attrelid='$f$||quote_ident(tab)||$f$'::regclass 
   AND attname='$f$||quote_ident(col)||$f$'
   $f$ INTO ret;
END;
$BODY$ LANGUAGE plpgsql;

-- FUNCTION get_crosstab_type(tab, row, val, cattab, catcol, catord)
--
-- This function generates the output type expression for the crosstab(text, text)
-- function from the PostgreSQL tablefunc module when the "categories" 
-- (cross table column labels) can be looked up in some view or table.
--
-- See auto_crosstab below for parameters
DROP FUNCTION get_crosstab_type(TEXT, TEXT, TEXT, TEXT, TEXT, TEXT);
CREATE FUNCTION get_crosstab_type(tab TEXT, rw TEXT, val TEXT, cattab TEXT, 
       catcol TEXT, catord TEXT, OUT ret TEXT)
AS $BODY$ BEGIN
EXECUTE $f$
    SELECT '"$f$||quote_ident(rw)||$f$" $f$
                   ||get_col_type(quote_ident(tab), quote_ident(rw))||$f$'
           || string_agg(',"'||_values._v||'" $f$
                   ||get_col_type(quote_ident(tab), quote_ident(val))||$f$')
    FROM (SELECT DISTINCT ON(_t.$f$||quote_ident(catord)||$f$) _t.$f$||quote_ident(catcol)||$f$ AS _v
          FROM $f$||quote_ident(cattab)||$f$ _t
          ORDER BY _t.$f$||quote_ident(catord)||$f$) _values
    $f$ INTO ret;
END; $BODY$ LANGUAGE plpgsql;

-- FUNCTION auto_crosstab_ordered(view_name, tab, row, cat, val, cattab, catcol, catord)
--
-- This function creates a VIEW containing a cross-table of input table.
-- It fetches the column names of the cross table ("categories") from
-- another table.
--
-- view_name - name of VIEW to be created
-- tab - input table. This table / view must have 3 columns:
--   "row", "category", "value".
-- row - column name of the "row" column
-- cat - column name of the "category" column
-- val - column name of the "value" column
-- cattab - another table holding the possible categories
-- catcol - column name in cattab to use as column label in the cross table
-- catord - column name in cattab to sort columns in the cross table
DROP FUNCTION auto_crosstab_ordered(TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT);
CREATE FUNCTION auto_crosstab_ordered(view_name TEXT, tab TEXT, rw TEXT,
    cat TEXT, val TEXT, cattab TEXT, catcol TEXT, catord TEXT) RETURNS void
AS $BODY$ BEGIN
EXECUTE $f$
CREATE VIEW $f$||quote_ident(view_name)||$f$ AS
  SELECT * FROM crosstab(
     'SELECT $f$||quote_ident(rw)||$f$,
         $f$||quote_ident(cat)||$f$,
         $f$||quote_ident(val)||$f$
     FROM $f$||quote_ident(tab)||$f$',
     'SELECT DISTINCT ON($f$||quote_ident(catord)||$f$) $f$||quote_ident(catcol)||$f$
      FROM $f$||quote_ident(cattab)||$f$ m 
      ORDER BY $f$||quote_ident(catord)||$f$'
     ) AS ($f$||get_crosstab_type(tab, rw, val, cattab, catcol, catord)||$f$)$f$;
END; $BODY$ LANGUAGE plpgsql;

-- FUNCTION auto_crosstab(view_name, tab, row, cat, val)
--
-- This function creates a VIEW containing a cross-table of input table.
-- It fetches the column names of the cross table ("categories") from
-- DISTINCT values of the 2nd column of the input table.
--
-- view_name - name of VIEW to be created
-- tab - input table. This table / view must have 3 columns:
--   "row", "category", "value".
-- row - column name of the "row" column
-- cat - column name of the "category" column
-- val - column name of the "value" column

DROP FUNCTION auto_crosstab(TEXT, TEXT, TEXT, TEXT, TEXT);
CREATE FUNCTION auto_crosstab(view_name TEXT, tab TEXT, rw TEXT, cat TEXT, val TEXT) RETURNS void
AS $$ BEGIN
    PERFORM auto_crosstab_ordered(view_name, tab, rw, cat, val, tab, cat, cat);
END; $$ LANGUAGE plpgsql;

Answer 4

I solved it folowing this article:

http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html

In short, i used function that for every result in a query dynamically creates the values for the next query, and returns the result needed as a refcursor. This solved my sql result part, now i need to figure out the java part, but that isnt connected much to the question :)

Answer 5

This is an example of pivoting data - in postgreSQL 9 there is a crosstab function to do this: http://www.postgresql.org/docs/current/static/tablefunc.html