I created a function with create function xxx(id uuid) returns void as $$.
Now, I would like to call it with select xxx(select id from mytable where ...); but this does not work. How can I do it?
|
Show 1 more comment
2 Answers
Just work on the ID and leave its source outside of the function call:
select xxx(id)
from mytable where ...;
Update for the downvoter: see it in action here
You cannot pass subquery as argument. One way is to use LATERAL JOIN:
SELECT xxx(s.id)
FROM table t1
LEFT JOIN LATERAL (select id from mytable where ... order by ... limit 1) s
ON true;
This is usefull if you need to do some correlation between main table and subquery.
If only one value:
SELECT xxx(s.id)
FROM (subquery) s;
or answer below.
3 Comments
Fla
I didn't know LATERAL, that's interesting. Can you tell me what's the
table t1 here though, and why ON true is needed?
Lukasz Szozda
@Fla I've thought that you need some sort of corellated subquery.
Fla
Alright so it allows to loop on it. But my subquery is damn simple, so yeah the answer below works oO. I really thought the select had to be done first, my bad. Thank you anyway.