Postgresql – Function returning table : access individual fields

plpgsqlpostgresql

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;

Best Answer

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.

Related Question