From: | Balázs Klein <Balazs(dot)Klein(at)t-online(dot)hu> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: dynamic crosstab |
Date: | 2008-02-20 15:11:37 |
Message-ID: | 20080220151138.B8BC4136AF0@mail01a.mail.t-online.hu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | Geoffrey | 2008-02-20 15:24:05 | Re: is a unique key on null field bad? |
Previous Message | Peter Childs | 2008-02-20 14:50:54 | Re: is a unique key on null field bad? |