What we have done in the past is dynamically build the crosstab query
while getting the field names through another query. Then we select the
query into a table and then select * from the table to get the result.
Sim
On 01/21/2011 07:34 PM, Julia Jacobson wrote:
Thanks for your answer. The fact that the column definition list must
be available to the parser was not obvious to me.
Instead of building the second query dynamically in an application, I
would prefer a user-defined function, because there will probably be
several applications in need of it and (please correct me if I'm
wrong) I hope for a better performance by creating a permanent view of
my crosstab query.
So I have tried something like:
CREATE FUNCTION denorm_table(mytable_name) RETURNS text AS '
DECLARE
tab_def_list TEXT;
BEGIN
# First query to create table definition list
SELECT DISTINCT rowdt::date INTO tab_def_list FROM mytable_name;
# Crosstab Query
SELECT * FROM crosstab
(
'SELECT rowid, rowdt, temperature FROM mytable ORDER BY 1',
'SELECT DISTINCT rowdt FROM mytable ORDER BY 1'
)
AS
(
rowid text,
tab_def_list
);
END;
' LANGUAGE plpgsql;
Being rather unexperienced in PL/pgSQL, I have problems to get the
code working.
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?
On 01/21/2011 16:08, Joe Conway wrote:
On 01/21/2011 05:48 AM, Julia Jacobson wrote:
Having read the documentation of the tablefunc module,
I'm wondering whether it is possible to get the values for the names of
the columns for the crosstab query from a subquery.
A minimal example would look like this:
CREATE TABLE mytable(rowid text, rowdt timestamp, temperature int);
INSERT INTO cth VALUES('test1','01 March 2003','42');
INSERT INTO cth VALUES('test2','02 March 2003','53');
INSERT INTO cth VALUES('test3','03 March 2003','49');
SELECT * FROM crosstab
(
'SELECT rowid, rowdt, temperature FROM mytable ORDER BY 1',
'SELECT DISTINCT rowdt FROM mytable ORDER BY 1'
)
AS
(
rowid text # This works fine
# Here should be a subquery to get column names
# automatically,
# like 'SELECT rowdt FROM mytable'
);
No, it is not possible. The column definition list needs to be available
to the parser. I usually recommend running this as two queries from your
application. The first does:
SELECT DISTINCT rowdt::date FROM mytable ORDER BY 1;
rowdt
------------
2003-03-01
2003-03-02
2003-03-03
(3 rows)
Then the application dynamically builds the second query and executes
it. Following your example, something like:
SELECT * FROM crosstab
(
$$SELECT rowid, rowdt::date, temperature FROM mytable ORDER BY 1$$,
$$VALUES('2003-03-01'),('2003-03-02'),('2003-03-03')$$
)
AS
(
rowid 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)
HTH,
Joe
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
