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; ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
