Function that takes id and returns the table - how to call it for multiple ids?

Question

I have a function that takes a scalar id value and returns the table. Let's say it looks like this:

CREATE FUNCTION get_id(num int) 
RETURNS TABLE(num int, name text) AS 
$$
SELECT num, name FROM t1
WHERE num = $1
$$ LANGUAGE SQL;

For the examplary tables t1 and t2:

CREATE TABLE t1(
    num int,
    name text);
    
INSERT INTO t1 VALUES 
    (1, 'a'),
    (2, 'b'),
    (3, 'c');

CREATE TABLE t2(
    num int,
    name text);
    
INSERT INTO t2 VALUES 
    (1, 'xxx'),
    (3, 'yyy'),
    (5, 'zzz');

I can call my get_id function as below:

SELECT * FROM get_id(1)

to get the results in a table form. But now I'd like to call it for multiple ids and stack the results together by rows. I tried the following:

SELECT get_id(num) FROM (
SELECT * FROM t2
INNER JOIN t1 USING(num)) AS foo;

but this returns me the composite rows. I get the reason, the effect is the actually the same as calling SELECT get_id(2) on the scalar value. But now I'd like to use my function to return results for many ids. Is it possible? If so, how can I achieve that?

Answer 1

That's what a lateral join is for:

SELECT t2.name, gi.*
FROM t2
  JOIN lateral get_id (t2.num) as gi on true

If you don't like the "fake" join condition on true, you can also use a cross join, as your function will only return a single row anyway:

SELECT t2.name, gi.*
FROM t2
  cross join lateral get_id (t2.num) as gi