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

Question

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?

Solution

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.

Comments

[Danubian Sailor]

Yes, but since the name of the cursor is not defined, I don't know it and I can't do the trick...

[Daniel Vérité]

Only if you use the auto-generated names. See the first example of the doc where the caller specifies a fixed name.

[Danubian Sailor]

Well, there's the fact that the author of procedure hasn't specified the fixed name. So I'm searching for general solution.

[Daniel Vérité]

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.

[Craig Ringer]

@lechlukasz The procedure returns the cursor name as refcursor

[VoidMain]

Check my answer in this thread: stackoverflow.com/a/12483222/657174 I think this method is easier tu use.