POSTGRES: Select query on a refcursor returned from a function

Question

I have a POSTGRES function that returns a refcursor as output. Printing the refcursor shows an output similar to this:

A     B
==========
1    dog
2    cat
3    tiger

My question is:

How can I execute a query on top of this refcursor? Something like:

select A from (function_returning_refcursor());

@gloomy.penguin Does that link suggest creating a type separately and return that instead of refcursor? If so, I don't want to do it that way. — M10TheMist
– M10TheMist, Commented Oct 2, 2014 at 22:36
This is exceedingly unclear. What's each refcursor a query over? What do you expect 'A' to be? What output do you expect? — Craig Ringer
– Craig Ringer, Commented Oct 3, 2014 at 1:07
I have posted the content of the refcursor above. Say the refcursor was populated by an initial query that selected everything from a couple of tables after a union. And the output looks something like the above. Now, I am trying select only a particular column from the above output i.e A. A's values have been shown as integers. — M10TheMist
– M10TheMist, Commented Oct 5, 2014 at 15:43

Craig Ringer · Accepted Answer · 2014-10-03 01:06:34Z

0

You'll need another function that consumes a refcursor and does whatever you want with the cursor contents. e.g.:

select f2(curs) from function_returning_refcursor() curs;

answered Oct 3, 2014 at 1:06

Craig Ringer

328k83 gold badges740 silver badges818 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

M10TheMist Over a year ago

Thanks for the reply. Can you tell me how that function f2 would do the query on the ref cursor? Which is basically what I don't understand and is my question.

Craig Ringer Over a year ago

@M10TheMist The PL/PgSQL documentation covers fetching from cursors: postgresql.org/docs/current/static/plpgsql-cursors.html . Note that you can also FETCH the results at the SQL level: postgresql.org/docs/current/static/sql-fetch.html

Collectives™ on Stack Overflow

POSTGRES: Select query on a refcursor returned from a function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related