2

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;
3

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.

  • 2
    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). – ypercubeᵀᴹ Jul 27 '17 at 1:03
  • 1
    Thanks, very clear. I will use CROSS JOIN LATERAL because explicit is better and also helps finding proper documentation. – JC Boggio Jul 27 '17 at 7:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.