Postgresql – Fill in a table name using a variable from another query

dynamic-sqlpostgresql

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

Best Answer

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

Related Solutions

Sql-server – Print Parameters in Dynamic SQL

One way to get this done is probably something you have already done, and that is to replace your line:

if @DebugMode=1 print @SQL

with

if @DebugMode=1 print @SQL + ' ' + convert(nvarchar(max), @Foobar)

And you would have to do it this way for all your variables, you will need to convert them manually to avoid conversion errors.

You could also use RAISERROR in a similar fashion:

if @DebugMode=1 RAISERROR (N'We used a value of %d for @Foobar', 10, 1, @Foobar)

HTH

PostgreSQL – Passing Array Between plpgsql Functions

You can avoid various complications by passing values as values with the USING clause:

CREATE OR REPLACE FUNCTION foo(linktable regclass, 
                               inttable  regclass, 
                               verttable regclass)
RETURNS void AS
$func$
BEGIN
   EXECUTE format(
      'SELECT tdgSetTurnInfo($1, $2, $3, array_agg(t.id)) FROM %s t'
     , linktable)
   USING  $1, $2, $3;  -- you could use parameter names as well
END
$func$ LANGUAGE plpgsql;

And pass the generated array directly in a single SELECT.

Note that $1, $2, $3 in EXECUTE reference the USING clause and only happen to be in sync with ordinal numbers of function parameters in USING $1, $2, $3. Same notation, independent scope!

After fixing the immediate cause for the error (like @Craig commented):

WHERE   int.id ANY %L

WHERE   int.id = ANY (%L)

Simplify the 2nd function in a similar fashion:

CREATE OR REPLACE FUNCTION bar(linktable regclass,
                               inttable  regclass,
                               verttable regclass,
                               intersection_ids int[])

    -- ... SNIP

    EXECUTE format('
        INSERT INTO %1$s
               (int_id, ref_link_id, match_link_id, ref_azimuth, match_azimuth)
        SELECT  int.id, l1.id      , l2.id
              , degrees(ST_Azimuth(ST_StartPoint(l1.geom),ST_EndPoint(l1.geom)))
                degrees(ST_Azimuth(ST_StartPoint(l2.geom),ST_EndPoint(l2.geom)))
        FROM    %2$s int
        JOIN    %3$s v1 ON v1.intersection_id = int.id
        JOIN    %3$s v2 ON v2.intersection_id = int.id
        JOIN    %4$s l1 ON l1.target_node = v1.node_id
        JOIN    %4$s l2 ON l2.source_node = v2.node_id
                     AND l2.road_id <> l1.road_id
        WHERE   int.id = ANY ($1)    -- fix syntax and pass as value!
        AND     l1.road_id IS NOT NULL
        AND     l2.road_id IS NOT NULL
        ', temptable,
           inttable,
           verttable,
           linktable)
    USING intersection_ids;

    ...

The whole setup might be simplified further.

Best Answer

Related Solutions

Sql-server – Print Parameters in Dynamic SQL

PostgreSQL – Passing Array Between plpgsql Functions

Related Question