Applying a function to all result rows of another function in PostgreSQL

We want to apply function bar() on every table row returned by function foo(), then return primary key of table returned by foo() together with all returned rows from bar().

Let’s define the two (pseudo) functions foo() and bar():

-- Function foo() returns some table in which
-- first column acts as primary key.
CREATE FUNCTION foo()
	RETURNING TABLE(aPk INT, b INT, c INT);

-- Function bar() takes primary key of foo() table and returns
-- some related data (without any reference to foo).
CREATE FUNCTION bar(aPk INT)
	RETURNING TABLE(d INT, e INT);

A more visual representation of what data these functions may return:

# SELECT * FROM foo();
 aPk | b | c
-----+---+---
  1  | 3 | 6
  2  | 5 | 2
  3  | 0 | 3
  
# SELECT * FROM bar(1);
 d | e
---+---
 7 | 8
 4 | 9

Desired result with values for aPk parameter=1 filled in:

 aPk | d | e
-----+---+---
  1  | 7 | 8
  1  | 4 | 9
  2  | . . .
  .
  .
  .

We can’t solve this with a simple CROSS JOIN (cartesian product) because we need to be able to access data returned from foo() to feed into bar().

Using LATERAL

PostgreSQL 9.3 introduced the LATERAL keyword for subqueries.

Subqueries appearing in FROM can be preceded by the key word LATERAL. This allows them to reference columns provided by preceding FROM items. (Without LATERAL, each subquery is evaluated independently and so cannot cross-reference any other FROM item.)

Which can be used like in following query to acquire the desired result:

SELECT foo.aPk, bar.*
FROM foo() foo, LATERAL (
	SELECT * FROM bar(foo.aPk)
) bar;
First published on January 29, 2015