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?

Erwin Brandstetter · Accepted Answer · 2014-03-14 16:44:31Z

3

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;

edited Mar 14, 2014 at 16:44

answered Mar 14, 2014 at 16:33

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

1 Comment

Fulano da Silva Sauro Over a year ago

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

Clodoaldo Neto · Accepted Answer · 2014-03-14 16:43:43Z

0

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

answered Mar 14, 2014 at 16:43

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Comments

Dwayne Towell · Accepted Answer · 2014-03-14 16:43:50Z

0

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;

answered Mar 14, 2014 at 16:43

Dwayne Towell

8,6635 gold badges39 silver badges54 bronze badges

1 Comment

Craig Ringer Over a year ago

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

Collectives™ on Stack Overflow

Dynamically execute query using the output of another query

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related