Geek asks and answers

How to select all rows from refcursor returned by the PL / pgSQL function?

I have some_func () function that returns refcursor:

CREATE OR REPLACE FUNCTION some_func() RETURNS refcursor AS (...)

I want to call this function from the console and display the result set from the cursor it returns. In Oracle, I would write:

SELECT * FROM TABLE(some_func());

What is equivalent to this PosgreSQL construct?

+5
source share
2 answers

The browser refers to its name, automatically generated or selected by you. This document page provides an example for everyone.

To get the results from refcursor, you must have a cursor name. In the case of the generated names, it will be kind of <unnamed portal 1>". Then you can:

FETCH ALL FROM "<unnamed portal 1>";

refcursor, .

+4

, postgres, , refcursor;

CREATE OR REPLACE FUNCTION "com.mkindika"."myfunction" ("refcursor", other input  parameters) RETURNS "pg_catalog"."refcursor" AS 
$body$
DECLARE

---- query

END;
$body$
LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;

refcursor, :

BEGIN;
SELECT "com.mkindika"."myfunction" ("refcursor",other input parameters);
FETCH ALL IN "refcursor";
0

More articles:


All Articles