I am doing a simple query inside a function, and I need to change the values of this query, but only for information. I don’t need to update the table as such. I managed to edit the values but I don’t know how to return them.
CREATE OR REPLACE FUNCTION setDetails(INTEGER,INTEGER) RETURNS TABLE ( id INTEGER, name TEXT, subsidy TEXT, stratum_id NUMERIC, price TEXT, total FLOAT ) AS $$ DECLARE service Record; subscibed_services Record; BEGIN for service in SELECT services.*, false as subscribed, (CAST(services.price AS float) - CAST(services.subsidy AS float)) AS total FROM services WHERE services.stratum_id = $1 loop for subscibed_services in SELECT services.id FROM services WHERE id IN (SELECT DISTINCT charge_details.service_id FROM charge_details WHERE charge_details.charge_id = $2) loop if (CAST(subscibed_services.id as INTEGER) = CAST(service.id as INTEGER)) then service.subscribed := true; EXIT; else service.total := 0; end if; end loop; end loop; END; $$ LANGUAGE plpgsql;
And below this function will be executed
SELECT setDetails(2,6320)
As you can see, the query does not bring me anything.
I appreciate the help and I apologize for using the google translator: D
Advertisement
Answer
If you want some result from table function, you should to fill related variables (defined inside table
clause list), and then you should to use RETURN NEXT
statement. It is well described by documentation.
Simple example:
CREATE OR REPLACE FUNCTION foo(n int) RETURNS TABLE(a int, b int) AS $$ BEGIN FOR i IN 1 .. n LOOP a := i; b := i + 1; RETURN NEXT; END LOOP; END; $$ LANGUAGE plpgsql; SELECT * FROM foo(10);
Your code has another performance bug. Two nested loops over queries can be slow. You can use JOIN
(in this case self join), and iterate over one cycle instead nested two.