1

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;
Improve this question
3
  • You can do that with dynamic SQL (google for EXECUTE) inside a PL/pgSQL function. Commented Feb 7, 2014 at 22:16
  • I see just looking for a little more direction how to execute Dynamic SQL. Trying to learn from this example stackoverflow.com/questions/12028041/… Commented Feb 7, 2014 at 22:41
  • 1
    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. Commented Feb 7, 2014 at 22:42

1 Answer 1

Reset to default
0

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 ...

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

1 Comment

Your query is different from the query in question. You need to move max/min aggregates to every subquery.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.