CODEWITHSUNDEEP
postgresqlcursorfetch
Evgeny
Evgeny

Reputation: 3322

PostgreSQL - displaying the data fetched by the cursor

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

Upvotes: 3

Views: 13062

Answers (1)

Chris Travers
Chris Travers

Reputation: 26454

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.

Upvotes: 5

Related Questions