Using a query result to set table of next query

Question

I have the following query that returns the min, max and user total of basically unique rows in two different tables.

select sum(user_count) as user_count_sum, sum(min_count) as min_count_sum, 
sum(max_count) as max_count_sum
from(
select max(case when di = 'i' then di_count end) as user_count, 
       min(di_count) as min_count,
       max(di_count) as max_count
from (
    select di, 
           count(distinct rt) as di_count
    from gpstablev2 
    where rt >  GETDATE() - '1 day'::INTERVAL
    group by di
)
UNION
select max(case when di = 'i' then di_count end) as user_count, 
       min(di_count) as min_count,
       max(di_count) as max_count
from (
    select di, 
           count(distinct rt) as di_count
    from powertablev2 
    where rt >  GETDATE() - '1 day'::INTERVAL
    group by di
)
)

Now I also have a master table, that if I were to run the following query would return the following

select table from mastertable;

gpstablev2
powertablev2
...(more table names)

Instead of pasting 9 more UNION blocks like the one I have to sum all the tables listed in mastertable, is there a way I can use a query to mastertable to clean this up?

If I were doing this in java I would have first get my ResultSet from master table and then do 10 queries and UNION them myself. But I rather have my database do ALL the processing, and be able to update based on the contents of mastertable.

EDIT: Based on feedback I am trying a Dynamic Query and have gotten this far, but am still have trouble putting together the rest of the pieces

CREATE OR REPLACE FUNCTION get_sums()
RETURNS TABLE(user_count_sum bigint, min_count_sum bigint, max_count_sum bigint)
$BODY$
BEGIN

RETURN QUERY EXECUTE "
SELECT $$SELECT sum(user_count) AS user_count_sum
      ,sum(min_count) AS min_count_sum
      ,sum(max_count) AS max_count_sum
FROM  (
   SELECT max(case when di = 'id' then di_count end) AS user_count
         ,min(di_count) AS min_count
         ,max(di_count) AS max_count
   FROM  ($$
||
string_agg(format($$
      (SELECT di, count(distinct rt) AS di_count
       FROM   %I
       WHERE  rt >  now() - interval '1 day'
       GROUP  BY 1)$$, tbl)
      ,'

      UNION ALL')
|| '
      ) sub1
   ) sub2'
FROM   mastertable;
INTO results_var"

END;
$BODY$
LANGUAGE plpqsql;

You can do that with dynamic SQL (google for `EXECUTE`) inside a PL/pgSQL function. — Ihor Romanchenko, Feb 07 '14 at 22:16
I see just looking for a little more direction how to execute Dynamic SQL. Trying to learn from this example http://stackoverflow.com/questions/12028041/select-a-dynamic-set-of-columns-from-a-table-and-get-the-sum-for-each/12031145#12031145 — Dan Ciborowski - MSFT, Feb 07 '14 at 22:41
Type definitions are missing. Should be: `RETURNS TABLE(user_count_sum bigint, min_count_sum bigint, max_count_sum bigint)`. The rest looks good now on a quick glance. — Erwin Brandstetter, Feb 07 '14 at 22:42

score 0 · Answer 1 · edited May 23 '17 at 10:25

You need dynamic SQL for that. SQL does not accept the dynamic conversion of literals / values to identifiers. So you need to build the query first and then execute it. You can do this in your client (seems to be Java), or do it all inside Postgres, which is generally fastest:

SELECT $$SELECT sum(user_count) AS user_count_sum
      ,sum(min_count)  AS min_count_sum
      ,sum(max_count)  AS max_count_sum
FROM  (
   ($$
||
string_agg(format($$
   SELECT max(case when di = 'i' then di_count end) AS user_count
         ,min(di_count) AS min_count
         ,max(di_count) AS max_count
   FROM  (
      SELECT di, count(distinct rt) AS di_count
      FROM   %I
      WHERE  rt >  now() - interval '1 day'
      GROUP  BY 1
      ) t
   )$$, tbl)
      ,'

   UNION ALL
   (')
|| '
   ) sub'
FROM   mastertable;

Produces the query-string:

SELECT sum(user_count) AS user_count_sum
      ,sum(min_count)  AS min_count_sum
      ,sum(max_count)  AS max_count_sum
FROM  (
   (
   SELECT max(case when di = 'i' then di_count end) AS user_count
         ,min(di_count) AS min_count
         ,max(di_count) AS max_count
   FROM  (
      SELECT di, count(distinct rt) AS di_count
      FROM   gpstablev2
      WHERE  rt >  now() - interval '1 day'
      GROUP  BY 1
      ) t
   )

   UNION ALL
   (
   SELECT max(case when di = 'i' then di_count end) AS user_count
         ,min(di_count) AS min_count
         ,max(di_count) AS max_count
   FROM  (
      SELECT di, count(distinct rt) AS di_count
      FROM   powertablev2
      WHERE  rt >  now() - interval '1 day'
      GROUP  BY 1
      ) t
   )

   UNION ALL
   ...

   ) sub;

GETDATE() is not a valid Postgres function. You probably mean now() or CURRENT_DATE .. depending on the undisclosed type of rt. I use now() instead.
You most probably want to use UNION ALL instead of UNION here.
Also fixed missing aliases for subqueries and made some minor simplifications.

Wrap this into a plpgsql function with EXECUTE. There are plenty of closely related examples here on SO with code and links and explanation ...

Your query is different from the query in question. You need to move max/min aggregates to every subquery. — Ihor Romanchenko, Feb 07 '14 at 22:24

Using a query result to set table of next query

1 Answers1