0

I have a general query I need to run for many tables

SELECT sum(honoraria_amount)
FROM table_1

The table IDs I have a list, from another query. For example, I want to run this for: table_1, table_39, table_42, etc.

Since I have a query that returns the IDs:

table_id
----
1
39
42

Is there anyway I can feed these query results, into the sum query, like some form of replacing with a variable or array to iterate through?

If its possible I'd like to replace x below with the IDs and maybe loop through all the queries to print out all the sums:

SELECT sum(honoraria_amount)
FROM table_x
0

Would it be acceptable for you to build a view with UNION ALL over all the tables and then make your select on this view? Like

CREATE VIEW union_table (id, name)
  AS      ( SELECT honoraria_amount,... FROM foreign_table_1 UNION ALL 
            SELECT honoraria_amount, ... FROM foreign_table_2 
  ) ;
0

Normally I'd advise against dynamic SQL as it can't be cached by the engine, but I don't see any other alternative that doesn't add a hurdle every time you wish to add a new one of these table_x's.

With that in mind, the following function will help you out:

create or replace function sum_table(
    tid integer
) returns numeric as $$
declare
    _out numeric;
begin
    execute 'select sum(honoraria_amount) from table_' || tid::text || ';' into _out;
    return _out;
end;
$$ language plpgsql;

And then to use it in a query:

with
    __tables as(
        select unnest(array[1, 39, 42]) as table_id
    )
select
    table_id,
    sum_table(table_id)
from
    __tables

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.