Function with cursor in postgreSQL

Question

I want to make a Function in postgreSQL that reads the result of a query with a cursor and returns the result in a table. I am not very familiar with cursors but I have make an effort with no result. The output was a blank table. Here is my code:

CREATE OR REPLACE FUNCTION getquery()
RETURNS TABLE(ID INT, Totalprice DECIMAL) AS $$

DECLARE 
query_cursor CURSOR FOR SELECT CustomerID, TotalDue  from SalesOrderHeader where TotalDue =( select max(TotalDue) from SalesOrderHeader);

 BEGIN
 OPEN query_cursor;

CLOSE query_cursor;

RETURN;
END;$$

LANGUAGE plpgsql;

CURSOR might be a synonym for "I want to go slow". There's even an antipattern for trying to work with one row at a time: RBAR - Row By Agonizing Row. Scenarios where a cursor makes sense are extremely rare. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 13, 2016 at 13:04

Patrick · Accepted Answer · 2016-06-13 13:00:00Z

You don't need a CURSOR at all to do this, you don't even need a function. But if you really want a CURSOR then you have to FETCH rows from it and return the results. You have to returns the results as a SETOF sometype because you can not combine a CURSOR with RETURNS TABLE. In general, that looks like this:

CREATE TYPE soh AS (ID integer, Totalprice decimal);

CREATE FUNCTION getquery() RETURNS SETOF soh AS $$
DECLARE 
  query_cursor CURSOR FOR SELECT CustomerID, TotalDue FROM SalesOrderHeader
                          WHERE TotalDue = (select max(TotalDue) from SalesOrderHeader);
  rec soh;
BEGIN
  OPEN query_cursor;
  FETCH query_cursor INTO rec.ID, rec.Totalprice;    -- Read a row from the cursor
  WHILE FOUND LOOP
    RETURN NEXT rec;                                 -- Return the data to the caller
    FETCH query_cursor INTO rec.ID, rec.Totalprice;  -- Keep on reading rows
  END LOOP;
  CLOSE query_cursor;
  RETURN;
END;
$$ LANGUAGE plpgsql;

However, your query will return only a single row so the LOOP is not necessary here. And your query will be more efficient like so:

SELECT CustomerID, TotalDue FROM SalesOrderHeader
ORDER BY TotalDue DESC LIMIT 1;

I tried that and now the output is only the 2 columns and the number of rows, no data.

Collectives™ on Stack Overflow

Function with cursor in postgreSQL

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related