1

My Postgres crosstab query reads:

SELECT mthreport.*
FROM crosstab
('SELECT 
to_char(ipstimestamp, ''mon DD HH24h'') As row_name, 
varid::text || log.varid || ''_'' || ips.objectname::text As bucket, 
COUNT(*)::integer As bucketvalue
FROM loggingdb_ips_boolean As log 
INNER JOIN IpsObjects As ips 
ON log.Varid=ips.ObjectId
GROUP BY to_char(ipstimestamp, ''yyyy MM DD HH24h''), row_name, bucket 
ORDER BY to_char(ipstimestamp, ''yyyy MM DD HH24h''), row_name, bucket')
As mthreport(item_name text,
jan3 integer, feb4 integer, mar5 integer)

Is there any way that I can pull the three items enumerated in the last line (jan3 integer etc.) from an ancillary query? I tried to substitute the last line with SELECT xyz FROM zyx, but that doesn't work.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
aag
  • 680
  • 2
  • 12
  • 33

1 Answers1

2

In the basic form of the query, you have to spell out the column names and types in the calling SELECT.

There are the crosstabN(text) variants that use a pre-defined return types.

For full automation you'll have to wrap each individual query into a function where you can predefine the return type. I posted a detailed example here.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228