postgres: how to call a function that returns a table and pass params from a query

Question

As an instant example:

A function that returns a table:

=> create or replace function testf(x integer)
     returns table(a integer, b integer)
   language sql
   as $function$
       select * from (values(1,2)) as foo(a,b)
   $function$;

Calling it so it returns a record (or whatever you call it):

=> select testf(3);
 testf
-------
 (1,2)

Calling it so it returns a table (good):

=> select * from  testf(3);
 a | b
---+---
 1 | 2

But how do I call it so the parameters come from a query?

=> select s.n, testf(s.n) from (select 3 as n union select 4) s;
 n | testf
---+-------
 3 | (1,2)
 4 | (1,2)            <-- not a table (or row with column names)


=> select * from  testf(s.n) from (select 3 as n) s;

 ERROR:  syntax error at or near "from"
 LINE 1: select * from  testf(s.n) from (select 3 as n) s;

Community · Accepted Answer · 2017-05-23 10:29:18Z

2

Use a LATERAL subquery:

SELECT *
FROM  (VALUES (3), (4)) AS s(n)
     , testf(s.n);

The comma is short notation for CROSS JOIN LATERAL, since LATERAL is assumed automatically with set-returning functions in the FROM clause.

What is the difference between LATERAL and a subquery in PostgreSQL?

edited May 23, 2017 at 10:29

CommunityBot

11 silver badge

answered Jan 27, 2017 at 14:16

Erwin Brandstetter

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

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

Collectives™ on Stack Overflow

postgres: how to call a function that returns a table and pass params from a query

A function that returns a table:

Calling it so it returns a record (or whatever you call it):

Calling it so it returns a table (good):

But how do I call it so the parameters come from a query?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

A function that returns a table:

Calling it so it returns a record (or whatever you call it):

Calling it so it returns a table (good):

But how do I call it so the parameters come from a query?

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related