Skip to content
Advertisement

How to edit and return a Cursor in plpgsql?

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.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement