Dynamically Execute Query Using The Output Of Another Query

I have a function called generate_table, that takes 2 input parameters (rundate::date and branch::varchar) Now I am trying to work on a second function, using PLPGSQL, that will ge

Solution 1:

You can do this with a plain SELECT query using the new LATERAL JOIN in Postgres 9.3+

SELECT*FROM  (
   SELECTmax(rundate) AS rundate, branch
   FROM   t_index_of_imported_files
   GROUPBY branch
   ) t
 , generate_table(t.rundate, t.branch) g;  -- LATERAL is implicit here

Per documentation:

LATERAL can also precede a function-call FROM item, but in this case it is a noise word, because the function expression can refer to earlier FROM items in any case.

The same is possible in older versions by expanding rows for set-returning functions in the SELECT list, but the new syntax with LATERAL is much cleaner. Anyway, for Postgres 9.2 or older:

SELECT generate_table(max(rundate), branch)
FROM   t_index_of_imported_files
GROUPBY branch;

Solution 2:

selectgenerate_table(max(rundate) as rundate, branch)
from t_index_of_imported_files
groupby branch

Solution 3:

No need to do anything too fancy.

SELECT generate_table(rundate,branch) FROM (
    SELECT max(rundate) AS rundate, branch
    FROM t_index_of_imported_files
    GROUPBY branch) x;