CODEWITHSUNDEEP
sqlpostgresqlcursorplpgsql
diligent
diligent

Reputation: 2352

when should i use fetch keyword in postgresql

I am new to pg cursor, I have a cursor here:

create function simplecur() returns refcursor as $$
declare 
    abc cursor for 
    select t.registrant, u.display_name from incident_info t, sys_user u
    where t.registrant != 10000000 and u.id = t.registrant
    group by t.registrant, u.display_name
    order by t.registrant ;
begin
    open abc;
    return abc;
end
$$language plpgsql;

I simply use 

select simplecur()

it returns 

abc

now, i want to know ,how could i get the result of my sql , when i use

fetch all from abc;

it show me like this:

ERROR:  cursor "abc" does not exist

Upvotes: 2

Views: 825

Answers (1)

user272735
user272735

Reputation: 10648

You need to have an open transaction when you're using the cursor variable:

begin;
  select simplecur();
  fetch all in abc;
commit;

Another SO example.

Upvotes: 1

Related Questions