postgres output query within with clause

Question

I'm trying to get the output of queries within the with clause of my final query as csv or some sort of text files. I only have query access, I'm not allowed to create tables for this database. I have a set of queries that do some calculations on a data set, another set of queries that compute on the previous set and yet another that calculates on the final set. I don't want to run all of it as three seperate queries because the results from the first two are actually in the last one.

WITH
    Q1 AS(
        SELECT col1, col2, col3, col4, col5, col6, col7
        FROM table1
    ),
    Q2 AS(
        SELECT AVG(col1) as col1Avg, MAX(col1) as col1Max, col2, col3,col4
        FROm Q1
        GROUP BY col2, col3, col4
    )
SELECT 
    AVG(col1AVG), col3
FROM
    Q2
GROUP BY col3

I would like the results from Q1, Q2 and the final select statement as preferably 3 csv files but I could live with all of it in one csv file. Is this possible?

Thanks!

Edit: Just to clarify, the columns from the queries are very different. I'm definitely pulling more columns from my first query than my second. I've edited the above code a bit to make this more clear.

Typically a csv file would have the same number of columns for each row. — David Aldridge
– David Aldridge, Commented May 10, 2013 at 14:25

David Aldridge · Accepted Answer · 2013-05-10 13:49:43Z

To combine all the results together you'd use UNION ALL, but the number and data types of the columns must match.

select col1, col2, col2
from   blah
union all
select col1, col2, col2
from   blah2
union all
... etc

You can reference CTE's in there of course ...

with
  cte_1 as (
    select ... from ...),
  cte_2 as (
    select ... from ... cte_1),
  cte_3 as (
    select ... from ... cte_2)
select col1, col2, col2
from   cte_1
union all
select col1, col2, col2
from   cte_2
union all
select col1, col2, col2
from   cte_3

If your final output is a csv then it looks like you have multiple row formats in there -- checksums? If so, in the queries that you union all together you might like to combine all the columns from each query into one string ...

with
  cte_1 as (
    select ... from ...),
  cte_2 as (
    select ... from ... cte_1),
  cte_3 as (
    select ... from ... cte_2)
select col1||','||col2||','||col2
from   cte_1
union all
select col1||','||col2
from   cte_2
union all
select col1
from   cte_3

Collectives™ on Stack Overflow

postgres output query within with clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related