Dynamic crosstab in postgres

Question

I'm using the Colpivot function to work around with this problem, basically I need to generate a dynamic crosstab like this one (Colpivot example):

 begin;

create temp table _test (
    year int,
    month int,
    country varchar,
    state varchar,
    income int
) on commit drop;

insert into _test values
    (1985, 01, 'sweden', '', 10),
    (1985, 01, 'denmark', '', 11),
    (1985, 01, 'usa', 'washington', 13),
    (1985, 02, 'sweden', '', 20),
    (1985, 02, 'usa', 'washington', 21),
    (1985, 03, 'sweden', '', 34),
    (1985, 03, 'denmark', '', 31),
    (1985, 03, 'usa', 'washington', 39),
    (1990, 12, 'sweden', '', 42),
    (1990, 12, 'denmark', '', 43),
    (1990, 12, 'usa', 'washington', 49),
    (1990, 12, 'germany', '', 45);

select colpivot('_test_pivoted', 'select * from _test',
    array['year', 'month'], array['country', 'state'], '#.income', null);

select * from _test_pivoted order by year, month;

-- returns:
--  year | month | 'denmark', '' | 'germany', '' | 'sweden', '' | 'usa', 'washington'
-- ------+-------+---------------+---------------+--------------+---------------------
--  1985 |     1 |            11 |               |           10 |                  13
--  1985 |     2 |               |               |           20 |                  21
--  1985 |     3 |            31 |               |           34 |                  39
--  1990 |    12 |            43 |            45 |           42 |                  49
-- (4 rows)

rollback;

colpivot function I'm using: https://github.com/hnsl/colpivot/blob/9f1f1db6d348cb78e7e921133a487ac569f89b4d/colpivot.sql

this library fulfills its function, the problem is that when a relation is empty it returns an error and not a table, what I would like to achieve is that when there are no related records it returns an empty table with the fields that were found

Answer 1

This link should help you out.

create or replace function colpivot(
    out_table varchar, in_query varchar,
    key_cols varchar[], class_cols varchar[],
    value_e varchar, col_order varchar
) returns void as $$
    declare
        in_table varchar;
        col varchar;
        ali varchar;
        on_e varchar;
        i integer;
        rec record;
        query varchar;
        -- This is actually an array of arrays but postgres does not support an array of arrays type so we flatten it.
        -- We could theoretically use the matrix feature but it's extremly cancerogenous and we would have to involve
        -- custom aggrigates. For most intents and purposes postgres does not have a multi-dimensional array type.
        clsc_cols text[] := array[]::text[];
        n_clsc_cols integer;
        n_class_cols integer;
    begin
        in_table := ('__' || out_table || '_in');
        -- if the temp table already exists, drop
        execute ( 'drop TABLE IF EXISTS ' || in_table );
        execute ('create temp table ' || in_table || ' on commit drop as ' || in_query);
        -- get ordered unique columns (column combinations)
        query := 'select array[';
        i := 0;
        foreach col in array class_cols loop
            if i > 0 then
                query := query || ', ';
            end if;
            query := query || 'quote_literal(' || quote_ident(col) || ')';
            i := i + 1;
        end loop;
        query := query || '] x from ' || in_table;
        for j in 1..2 loop
            if j = 1 then
                query := query || ' group by ';
            else
                query := query || ' order by ';
                if col_order is not null then
                    query := query || col_order || ' ';
                    exit;
                end if;
            end if;
            i := 0;
            foreach col in array class_cols loop
                if i > 0 then
                    query := query || ', ';
                end if;
                query := query || quote_ident(col);
                i := i + 1;
            end loop;
        end loop;
        -- raise notice '%', query;
        for rec in
            execute query
        loop
            clsc_cols := array_cat(clsc_cols, rec.x);
        end loop;
        n_class_cols := array_length(class_cols, 1);
        n_clsc_cols := array_length(clsc_cols, 1) / n_class_cols;
        -- build target query
        query := 'select ';
        i := 0;
        foreach col in array key_cols loop
            if i > 0 then
                query := query || ', ';
            end if;
            query := query || '_key.' || quote_ident(col) || ' ';
            i := i + 1;
        end loop;
        for j in 1..n_clsc_cols loop
            query := query || ', ';
            col := '';
            for k in 1..n_class_cols loop
                if k > 1 then
                    col := col || ', ';
                end if;
                col := col || clsc_cols[(j - 1) * n_class_cols + k];
            end loop;
            ali := '_clsc_' || j::text;
            query := query || '(' || replace(value_e, '#', ali) || ')' || ' as ' || quote_ident(col) || ' ';
        end loop;
        query := query || ' from (select distinct ';
        i := 0;
        foreach col in array key_cols loop
            if i > 0 then
                query := query || ', ';
            end if;
            query := query || quote_ident(col) || ' ';
            i := i + 1;
        end loop;
        query := query || ' from ' || in_table || ') _key ';
        for j in 1..n_clsc_cols loop
            ali := '_clsc_' || j::text;
            on_e := '';
            i := 0;
            foreach col in array key_cols loop
                if i > 0 then
                    on_e := on_e || ' and ';
                end if;
                on_e := on_e || ali || '.' || quote_ident(col) || ' = _key.' || quote_ident(col) || ' ';
                i := i + 1;
            end loop;
            for k in 1..n_class_cols loop
                on_e := on_e || ' and ';
                on_e := on_e || ali || '.' || quote_ident(class_cols[k]) || ' = ' || clsc_cols[(j - 1) * n_class_cols + k];
            end loop;
            query := query || 'left join ' || in_table || ' as ' || ali || ' on ' || on_e || ' ';
        end loop;
        -- raise notice '%', query;
        execute ('create temp table ' || out_table || ' as ' || query);
        -- cleanup temporary in_table before we return
        execute ('drop table ' || in_table);
        return;
    end;
$$ language plpgsql volatile;