Dynamically execute query using the output of another query

Question

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 get a list of all branches and the newest date for each branch and pass this as a parameter to the generate_table function.

The query that I have is this:

select max(rundate) as rundate, branch
from t_index_of_imported_files
group by branch

and it results on this:

rundate;branch 
2014-03-13;branch1
2014-03-12;branch2
2014-03-10;branch3
2014-03-13;branch4

and what I need is that the function run something like this

select generate_table('2014-03-13';'branch1');
select generate_table('2014-03-12';'branch2');
select generate_table('2014-03-10';'branch3');
select generate_table('2014-03-13';'branch4');

I've been reading a lot about PLPGSQL but so far I can only say that I barely know the basics.

I read that one could use a concatenation in order to get all the values together and then use a EXECUTE within the function, but I couldn't make it work properly.

Any suggestions on how to do this?

Gamopetalous

Answer 1

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

SELECT *
FROM  (
   SELECT max(rundate) AS rundate, branch
   FROM   t_index_of_imported_files
   GROUP  BY 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
GROUP  BY branch;

Chaffee

Wow! Much simpler than I thought! Thanks a lot for all who answered! – Gamopetalous

Answer 2

select generate_table(max(rundate) as rundate, branch)
from t_index_of_imported_files
group by branch

Dilemma

Answer 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
    GROUP BY branch) x;

Merlynmermaid

Set-returning functions in SELECT are deprecated now that LATERAL is supported, and can have weird and surprising results. – Elman