when should i use fetch keyword in postgresql

Question

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

Community · Accepted Answer · 2017-05-23 12:13:19Z

1

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.

edited May 23, 2017 at 12:13

CommunityBot

11 silver badge

answered Aug 1, 2012 at 5:09

user272735

10.6k9 gold badges72 silver badges100 bronze badges

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

Collectives™ on Stack Overflow

when should i use fetch keyword in postgresql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related