postgres crosstab query with $libdir/tablefunc crosstab_hash function

Question

My crosstab query (see below) runs just fine. However, I have to generate a large number of such queries, and - crucially - the number of column definitions will vary from day to day. If the number of output columndefs does not match that of the second argument of the crosstab, the crosstab will throw and error and abort. Therefore, I cannot "hard-wire" the column definitions as in my current query, and I need instead a function which will ensure that column definitions will be synchronized on-the-fly. Is it possible to write a generic postgres function that will be reusable in all such instances? Here is my query:

SELECT *
FROM crosstab
('SELECT 
to_char(ipstimestamp, ''mon DD HH24h'') As row_name, 
ips.objectid::text As category, 
COUNT(*)::integer As value
FROM loggingdb_ips_boolean As log 
INNER JOIN IpsObjects As ips 
ON log.Varid=ips.ObjectId
WHERE ((    log.varid = 37551) 
OR (log.varid = 27087)
OR (log.varid = 29469)   
OR (log.varid = 50876)
OR (log.varid = 45096)
OR (log.varid = 54708)
OR (log.varid = 47475)
OR (log.varid = 54606)
OR (log.varid = 25528)
OR (log.varid = 54729))
 GROUP BY to_char(ipstimestamp, ''yyyy MM DD HH24h''), row_name, objectid, category     
 ORDER BY to_char(ipstimestamp, ''yyyy MM DD HH24h''), row_name, objectid, category', 

 'SELECT DISTINCT varid 
 FROM loggingdb_ips_boolean ORDER BY 1;' 
 )

 As CountsPerHour(row_name text,  
 "25528" integer,
 "27087" integer,
 "29469" integer,
 "37551" integer,
 "45096" integer,
 "54606" integer,
 "54708" integer,
 "54729" integer)

PS: Note that this query can be run against test data at the following server: host: bellariastrasse.com database: IpsLogging user: guest password: guest

Erwin Brandstetter · Answer 1 · 2012-06-22T22:59:07.893

I am afraid what you want is not completely possible. If the return type varies, you can either

create a function returning a generic SETOF record.

But then you'd have to provide a column definition list with every call - bringing you right back to where you started.

create a new function with a matching return type for every different case.

But that's what you are trying to avoid ...

If you have to write "a large number of such queries" you could utilize a query-generator function instead, which would not return the results but the DDL script which you would execute in a second step. Basically a function that takes in the variable parts as parameters and generates the query string in your example .. RETURNS text.

This can get pretty complex. Several meta-levels on top of each other have to be considered, but it is absolutely possible. Be sure to make heavy use of dollar-quoting to keep the quoting madness at bay.

postgres crosstab query with $libdir/tablefunc crosstab_hash function

1 Answers1