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=r.idright;