Re: Subquery for column names of tablefunc crosstab queries

On 01/21/2011 09:34 AM, Julia Jacobson wrote:
> Two further problems are the fact that the names of columns are not
> allowed to begin with a number and every entry in the table definition
> list must not only contain the name of the column but of course also a
> data type (always the same - int).
> Is it possible for a newbie to solve my problem by a user-defined
> function in PL/pgSQL or is it rather complicated?

It's a bit tricky to get correct. The following is a bit ugly, and just
barely tested, but works at least for your presented case:

-------------------------------------------
CREATE OR REPLACE FUNCTION generate_crosstab_sql(relname text,
grpattr text,
grpattrtyp text,
catattr text,
valattr text,
valattrtyp text,
whereclause text)
RETURNS text AS $$
DECLARE
crosstabsql text;
coldef text;
catdef text;
rec record;
BEGIN
coldef := '(' || grpattr || ' ' || grpattrtyp;
catdef := 'VALUES';
FOR rec IN EXECUTE
'SELECT DISTINCT ' || catattr ||
' AS c FROM ' || relname || ' WHERE ' ||
whereclause || ' ORDER BY 1'
LOOP
coldef := coldef || ',"' || rec.c || '" ' || valattrtyp;
IF catdef = 'VALUES' THEN
catdef := catdef || '($v$' || rec.c || '$v$)';
ELSE
catdef := catdef || ',($v$' || rec.c || '$v$)';
END IF;
END LOOP;
coldef := coldef || ')';

IF catdef != 'VALUES' THEN
crosstabsql :=
$ct$SELECT * FROM crosstab ('SELECT $ct$ ||
grpattr || $ct$,$ct$ ||
catattr || $ct$,$ct$ || valattr ||
$ct$ FROM $ct$ || relname ||
$ct$ WHERE $ct$ || whereclause ||
$ct$ ORDER BY 1,2','$ct$ ||
catdef || $ct$') AS $ct$ || coldef;
END IF;
RETURN crosstabsql;
END;
$$ LANGUAGE plpgsql;
-------------------------------------------

Then this call:
-------------------------------------------
SELECT generate_crosstab_sql('mytable',
'rowid',
'text',
'rowdt::date',
'temperature',
'int',
'1 = 1');
-------------------------------------------

Produces this SQL:
-------------------------------------------
SELECT * FROM crosstab ('SELECT rowid,rowdt::date,temperature FROM
mytable WHERE 1 = 1 ORDER BY
1,2','VALUES($v$2003-03-01$v$),($v$2003-03-02$v$),($v$2003-03-03$v$)')
AS (rowid
text,"2003-03-01" int,"2003-03-02" int,"2003-03-03" int)
-------------------------------------------

Which produces this result:
-------------------------------------------
SELECT * FROM crosstab ('SELECT rowid,rowdt::date,temperature FROM
mytable WHERE 1 = 1 ORDER BY
1,2','VALUES($v$2003-03-01$v$),($v$2003-03-02$v$),($v$2003-03-03$v$)')
AS (rowid
contrib_regression(# text,"2003-03-01" int,"2003-03-02" int,"2003-03-03"
int);
rowid | 2003-03-01 | 2003-03-02 | 2003-03-03
-------+------------+------------+------------
test1 | 42 | |
test2 | | 53 |
test3 | | | 49
(3 rows)
-------------------------------------------

You might need some adjustments to get this to do exactly what you want.
Also please test it for correctness ;-)

HTH,

Joe

--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support