I always hope that somebody might have something similar but
> generic - eg. create those columns automatically and just treat them all
> as text.
I came up with this amateurish one based on http://www.ledscripts.com/tech/article/view/5.html.
Maybe someone can use it:
takes
- a select statement
- a name for the resulting view
- the column name of the id
- the column name of the attribute
- the column name of the value
- the aggregate function used
It recreates the view of the given name as a crosstab of the sql specified.
CREATE OR REPLACE FUNCTION "public"."create_crosstab_view" (eavsql_inarg varchar, resview varchar, rowid varchar, colid
varchar,val varchar, agr varchar) RETURNS "pg_catalog"."void" AS
$body$
DECLARE
casesql varchar;
dynsql varchar;
r record;
BEGIN
dynsql='';
for r in
select * from pg_views where lower(viewname) = lower(resview)
loop
execute 'DROP VIEW ' || resview;
end loop;
casesql='SELECT DISTINCT ' || colid || ' AS v from (' || eavsql_inarg || ') eav ORDER BY ' || colid;
FOR r IN EXECUTE casesql Loop
dynsql = dynsql || ', ' || agr || '(CASE WHEN ' || colid || '=' || r.v || ' THEN ' || val || ' ELSE NULL END) AS '
||agr || '_' || r.v;
END LOOP;
dynsql = 'CREATE VIEW ' || resview || ' AS SELECT ' || rowid || dynsql || ' from (' || eavsql_inarg || ') eav GROUP BY
'|| rowid;
EXECUTE dynsql;
END
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
