CODEWITHSUNDEEP
postgresql
Przemek
Przemek

Reputation: 6710

Function returning a table used in a SELECT statement

I have a function returning a table, defined like this:

CREATE OR REPLACE FUNCTION my_function(IN id_in bigint) RETURNS TABLE (
    out_1 bigint,
    out_2 text
) AS
$$
BEGIN
    -- Do something and return the appropriate result
END;
$$
LANGUAGE plpgsql;

When I do something like this:

SELECT * FROM my_function(1)

It returns a table, as expected. However, I would like to use it to do something a little more advanced, like:

SELECT my_function(id) FROM some_table;

The query works, but I have a little trouble working with the result. It returns a set of records, like:

(1,text_1)

(2,text_2)

etc.

It's expected behavior, however is there a way to "unpack" these records into a "normal" table, just like the one a SELECT * FROM returns?

Upvotes: 0

Views: 101

Answers (1)

klin
klin

Reputation: 121854

Use lateral join. A function used in FROM clause is lateral by default:

SELECT f.* 
FROM some_table, my_function(id) AS f;

Upvotes: 1

Related Questions