Function returning table : access individual fields

Question

I have a plpgsql function declared like this :

CREATE OR REPLACE FUNCTION dynamiccondition_byid(idcond INTEGER, OtherFilters TEXT)
RETURNS TABLE (id INTEGER, weight INTEGER)

I can get the individual values like this :

SELECT * FROM dynamiccondition_byid(1,'');

Now I have a table with several conditions and I want to get all the values for all conditions.

SELECT iddyncondition,dynamiccondition_byid(iddyncondition,'')
FROM dynconditions 

Now I'm getting two columns : one with the iddyncondition and one with the result of the function in which the 2 values are combined with parens :

iddyncondition dynamiccondition_byid
-------------- ---------------------
1 (1234,5)
2 (5678,10)

I want to use the individual values (these are not arrays, I don't know what they are really).

The only way I have found is the following but I'm pretty sure the function is called twice for each line and performance is an issue :

SELECT iddyncondition
 ,(dynamiccondition_byid(iddyncondition,'')).id
 ,(dynamiccondition_byid(iddyncondition,'')).weight
FROM dynconditions 

I would like to retrieve the data in a CTE like this (I'm using an array syntax so you get the idea but I know it is wrong) :

WITH dc AS (
 SELECT iddyncondition,dynamiccondition_byid(iddyncondition,'') AS dcrec
 FROM dynconditions 
)
SELECT r.amountpaid,dc.weight
FROM rights r
JOIN royaltiesad ra USING(idoeu)
JOIN dc ON dc.iddyncondition=ra.iddyncondition and dc.dcrec[0]=r.idright;

Answer 1

You are right, the function will be called for each column if it used in the select clause even if it declared as immutable. The simple example:

drop function if exists foo(int);
create function foo(x int, out a int, out b text) returns record immutable language plpgsql as $$ 
begin
 raise info 'foo() called';
 a := x*2;
 b := 'x';
 return;
end $$;
explain (analyse, verbose)
 select *, (foo(t.x)).*
 from (select 2 as x) as t;

INFO: foo() called
INFO: foo() called
┌────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞════════════════════════════════════════════════════════════════════════════════════╡
│ Result (cost=0.00..0.51 rows=1 width=0) (actual time=5.189..5.190 rows=1 loops=1) │
│ Output: 2, (foo(2)).a, (foo(2)).b │
│ Planning time: 0.087 ms │
│ Execution time: 5.239 ms │
└────────────────────────────────────────────────────────────────────────────────────┘

There are several notes:

If your function is not SRF, you could to declare it as returns record and use out parameters to give the names to the returning fields;

I used shorthand to retrieve all fields from the function.

The right way is to use cross join:

explain (analyse, verbose)
 select *
 from (select 2 as x) as t cross join lateral foo(t.x);

INFO: foo() called
┌──────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞══════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Function Scan on nd.foo (cost=0.25..0.26 rows=1 width=36) (actual time=0.534..0.535 rows=1 loops=1) │
│ Output: 2, foo.a, foo.b │
│ Function Call: foo(2) │
│ Planning time: 0.114 ms │
│ Execution time: 0.611 ms │
└──────────────────────────────────────────────────────────────────────────────────────────────────────┘

Or for your case:

WITH dc AS (
 SELECT iddyncondition, id, weight AS dcrec
 FROM dynconditions CROSS JOIN LATERAL dynamiccondition_byid(iddyncondition,'') 
)

Update: following to the comment instead of cross join lateral you also can use: t cross join foo(t.x), t, lateral foo(t.x) or just t, foo(t.x) - all will works in same way. But I recommend to use at least cross join form for that case if some other tables will be involved in the query which makes it more clear.

• ypercubeᵀᴹ: Besides from t cross join lateral foo(t.x) you can also use from t, lateral foo(t.x) or just from t, foo(t.x).
• JC Boggio: Thanks, very clear. I will use CROSS JOIN LATERAL because explicit is better and also helps finding proper documentation.