Re: dynamic crosstab

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;