postgres - dynamic pivot-table cursor with column array aggregation

postgres_pivotcursor.sql

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