Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save RedShift1/77d08c22ec76da6db8bba97f2fbb2bfe to your computer and use it in GitHub Desktop.
Save RedShift1/77d08c22ec76da6db8bba97f2fbb2bfe to your computer and use it in GitHub Desktop.
postgres - dynamic pivot-table cursor with column array aggregation
--- description: creates a dynamic temporary pivot table:
--- first argument is the name of the pivot-table
--- second the data select
--- third an array of key fields
--- fourth an array of pivot columns
--- fifth an array of returning fields
---
--- usage (example): select * from pivottable( 'mypivot', 'select * from mydata', array['id', 'name', 'description'], array['parametername'], array['parametervalue::text', 'parametervaluetext'] ); fetch all from mypivot;
CREATE OR REPLACE FUNCTION pivottable(
p_outtable refcursor,
p_dataquery text,
p_keycolumns text[],
p_pivotcolumns text[],
p_datacolumns text[])
RETURNS SETOF refcursor
LANGUAGE 'plpgsql'
COST 100
VOLATILE
ROWS 1000
AS $BODY$
declare
l_keytable name default '_key';
l_intable text;
l_rec record;
l_pivotkeys text[];
l_pivottable text;
l_join text;
l_joinpart text;
l_datafields text;
l_fieldname text;
begin
l_intable := quote_ident( '__' || p_outtable::text || '_in' );
--- build temp table for pivot structure
--- get first all possible keys of the pivot columns
execute( 'create temp table ' || l_intable || ' on commit drop as ' || p_dataquery );
for l_rec in
execute( 'select array[' || array_to_string( p_pivotcolumns, '::text, ' ) || '::text ] as keys from ' || l_intable || ' group by ' || array_to_string( p_pivotcolumns, ', ' ) || ' order by keys' )
loop
l_pivotkeys := array_cat( l_pivotkeys, array[ array_to_string( l_rec.keys, '|' ) ] );
end loop;
--- build join structure of key elements and pivot elements
--- for each pivot-key a left-join is build with the key reference names and the pivot data reference
l_join := '';
l_datafields := '';
for i in 1..array_length( l_pivotkeys, 1 ) loop
--- numerical pivot table name
l_pivottable := '_pivot' || i::text;
--- start left-join fir this table and build key-column reference
l_joinpart := 'left join ' || l_intable || ' as ' || l_pivottable || ' on ';
for n in 1..array_length( p_keycolumns, 1 ) loop
l_joinpart := l_joinpart || l_pivottable || '.' || p_keycolumns[n] || ' = ' || l_keytable || '.' || p_keycolumns[n] || ' and ';
end loop;
--- append left-join with pivot-columns and the value part
for n in 1..array_length( p_pivotcolumns, 1 ) loop
l_fieldname := split_part( l_pivotkeys[i], '|', n );
l_joinpart := l_joinpart || l_pivottable || '.' || p_pivotcolumns[n] || ' = ' || quote_literal( l_fieldname ) || ' and ';
l_datafields := l_datafields || ', array[' || l_pivottable || '.' || array_to_string( p_datacolumns , ', ' || l_pivottable || '.' ) || '] as ' || l_fieldname;
end loop;
l_join := l_join || ' ' || substr( l_joinpart , 1, char_length( l_joinpart ) - 5 );
end loop;
--- build result cursor with final pivot table
open p_outtable scroll for execute 'select distinct ' || l_keytable || '.'
|| array_to_string( p_keycolumns , ', ' || l_keytable || '.' )
|| l_datafields
|| ' from ' || l_intable || ' as ' || l_keytable
|| ' ' || l_join;
return next p_outtable;
end
$BODY$;
COMMENT ON FUNCTION pivottable(refcursor, text, text[], text[], text[])
IS 'creates a dynamic temporary pivot table, first argument is the name of the pivot-table, second the data select, third an array of key fields, fourth an array of pivot columns, fifth an array of returning fields';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment