PostgreSQL crosstab query pulling columns from a helper query

Question

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.

Do you have your answer?

Erwin Brandstetter
– Erwin Brandstetter

2017-01-18 22:30:42 +00:00
Commented Jan 18, 2017 at 22:30 — Erwin Brandstetter
– Erwin Brandstetter, Commented Jan 18, 2017 at 22:30

Community · Accepted Answer · 2017-05-23 12:01:56Z

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.

edited May 23, 2017 at 12:01

CommunityBot

11 silver badge

answered Jun 18, 2012 at 0:10

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL crosstab query pulling columns from a helper query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related