Creating a cross tab / pivot table form hstore fields with dynamical column names using in postgresql

hstore_pivot.sql

DROP TABLE IF EXISTS survey_sessions;
-- Imagine a table with survey sessions
-- token: some id or token to access a survey
-- answers: a key value store for answers
CREATE TABLE survey_sessions (
  token text,
  answers hstore);

-- We need some data to play with
INSERT INTO survey_sessions (token, answers) VALUES ('9IaxxP', 'a=>1, b=>2');
INSERT INTO survey_sessions (token, answers) VALUES ('TA2ZZ', 'b=>5, c=>6');
INSERT INTO survey_sessions (token, answers) VALUES ('IRQA', 'a=>7, c=>8');

DROP TABLE IF EXISTS temp1;
-- First we need a key value view combined over all sessions
CREATE TEMP TABLE temp1 AS SELECT token, (each(answers)).key as key, (each(answers)).value as value FROM survey_sessions;

-- 1. We define a function pivotanswers that creates a pivot table with dynamic columns
-- Inspired and adapted from from Eric Minikel, CureFFI.org - 2013-03-19 
-- http://www.cureffi.org/2013/03/19/automatically-creating-pivot-table-column-names-in-postgresql/

-- prerequisite: install the tablefunc module
--create extension tablefunc;

-- tablename: name of source table you want to pivot
-- rowc: the name of the column in source table you want to be the rows
-- colc: the name of the column in source table you want to be the columns
-- cellc: an aggregate expression determining how the cell values will be created
-- celldatatype: desired data type for the cells
create or replace function pivotanswers (tablename varchar, rowc varchar, colc varchar, cellc varchar, celldatatype varchar) returns varchar language plpgsql as $$
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
    dynsql2 = 'CREATE TEMP TABLE results AS 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 ct (
 '||rowc||' varchar,'||columnlist||'
 );';
 -- Here we simply execute creating the temporary table, I could not figure out how to return a whole table
 DROP TABLE IF EXISTS results;
 EXECUTE dynsql2;
 RETURN dynsql2;
END;
$$;

-- Now we create a pivot table with automatic column names. 
SELECT pivotanswers('temp1','token','key','max(value)','text');
SELECT * from results -- Now if that's not cool, i don't know what is.

That's useful stuff sir! Thanks for the insigth

dynsql1 = 'select string_agg(distinct ''"''||'||colc||'||''" '||celldatatype||''','','' order by ''"''||'||colc||'||''" '||celldatatype||''') from '||tablename||';';

This will allow the column that becomes the column names to have spaces