6

I have a function some_func() that returns refcursor:

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

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

SELECT * FROM TABLE(some_func());

What is the equivalent of that construction on PosgreSQL?

Danubian Sailor
  • 1
  • 38
  • 145
  • 223

2 Answers2

5

A refcursor is referred to by its name, either auto-generated or chosen by you. This page of the doc gives an example for each.

To fetch results from a refcursor you must have the cursor's name. In the case of generated names that'll be something like <unnamed portal 1>". You can then:

FETCH ALL FROM "<unnamed portal 1>";

The cursor name is returned from the function as the refcursor result, so you can get it from there.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
  • Yes, but since the name of the cursor is not defined, I don't know it and I can't do the trick... – Danubian Sailor Jun 21 '12 at 11:13
  • Only if you use the auto-generated names. See the first example of the doc where the caller specifies a fixed name. – Daniel Vérité Jun 21 '12 at 11:37
  • Well, there's the fact that the author of procedure hasn't specified the fixed name. So I'm searching for general solution. – Danubian Sailor Jun 21 '12 at 11:44
  • In general with PG for `select * from func()` one would use a function that returns `SETOF something` and not a refcursor. It's not clear whether you're stuck with an existing design where refcursors are already used or if you're in the design phase, in which case you might want to reconsider in favor of SETOF. – Daniel Vérité Jun 21 '12 at 12:29
  • @lechlukasz The procedure *returns* the cursor name as `refcursor` – Craig Ringer Aug 24 '12 at 01:42
  • 1
    Check my answer in this thread: http://stackoverflow.com/a/12483222/657174 I think this method is easier tu use. – VoidMain Sep 18 '12 at 18:56
0

Lets say you have a postgres function written in such a way that return a 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;

If you want to print the refcursor you may use following lines of code;

BEGIN;
SELECT "com.mkindika"."myfunction" ("refcursor",other input parameters);
FETCH ALL IN "refcursor";
indika
  • 821
  • 7
  • 13