pivot.sql

CREATE OR REPLACE FUNCTION pivot(
     refcursor, tablename character varying,
     rowc character varying,
     colc character varying,
     cellc character varying,
     celldatatype character varying)
     RETURNS refcursor
     LANGUAGE 'plpgsql'

     COST 100
     VOLATILE
 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
     --tablename = REPLACE(text, ''', E'\\"')
     dynsql2 = 'select * from crosstab (
  ''select '||rowc||','||colc||','||cellc||' from '||replace(tablename, chr(39),E'\'\'')||' group by 1,2 order by 1,2'',
  ''select distinct '||colc||' from '||replace(tablename, chr(39),E'\'\'')||' order by 1''
  )
  as newtable (
  '||rowc||' varchar,'||columnlist||'
  );';
     OPEN $1 FOR EXECUTE dynsql2;
     RETURN $1;
 end
 $BODY$;
 
 -- USAGE
 
 -- BEGIN
 -- SELECT pivot('my_cursor'::refcursor, 'precos', 'id_produto', 'id_preco', 'sum(valor)', 'numeric(10,2)')
 -- FETCH ALL IN my_cursor
 -- COMMIT