PostgreSQL - displaying the data fetched by the cursor

Question

I've successfully created the function that returns the set of cursors, i.e:

CREATE OR REPLACE FUNCTION select_multiple(refcursor, refcursor)
  RETURNS SETOF refcursor AS
$BODY$
BEGIN
  OPEN $1 FOR SELECT testtemptable.myid FROM testtemptable;   -- Open the first cursor
  RETURN NEXT $1;                                                                              -- Return the cursor to the caller

  OPEN $2 FOR SELECT testtemptable.name FROM testtemptable;   -- Open the second cursor
  RETURN NEXT $2;                                                                              -- Return the cursor to the caller
END;
$BODY$
  LANGUAGE plpgsql VOLATILE

I call the function and I expect each cursor to return 5 rows, which looks like it's happening. Here is what I do to call it:

BEGIN;
select select_multiple('a', 'b');
FETCH ALL IN "a";
FETCH ALL IN "b";
COMMIT;

My question is, however, how can I "visualize" the data to verify what's returned? The "Data Output" tab show nothing at all, and the "Messages" tab shows

Query result with 2 rows discarded.
Query result with 5 rows discarded.
Query result with 5 rows discarded.
Query returned successfully with no result in 11 ms.

Where have my results been discarded and how can I display them in the "Data Output" tab instead? PostgreSQL version 9.1

Your script works fine for me in psql. Are you having problems specifically with pgAdmin3? — Chris Travers
– Chris Travers, Commented Mar 20, 2013 at 9:30
Yes, this is pgAdmin3 specific, I think. I'll try running my scripts in psql if I have to, thanks. — Evgeny
– Evgeny, Commented Mar 21, 2013 at 3:36

Chris Travers · Accepted Answer · 2013-03-20 09:40:36Z

5

Your basic problem is that pg_admin won't display results from multi-query blocks.

What you need to do is run each of the statements in order as a separate query.

I.e.

Begin; (run)
SELECT * FROM .... ; (run)
FETCH ALL FROM "a"; (run);
etc.

Then it will work as expected.

answered Mar 20, 2013 at 9:40

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

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

1 Comment

Damien Sawyer Over a year ago

As a follow up to this, the new "Azure Data Studio" will allow you to return multiple result sets. You can set these types of queries up now and execute in one pass.

Collectives™ on Stack Overflow

PostgreSQL - displaying the data fetched by the cursor

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related