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