nested cursor loop in postgresql

Question

I am new to postgresql, and get a problem about nested loop.Here is my code:

CREATE TABLE q_39442172
(
   id character varying,
   event_id character varying,
   createdat character varying
);


insert into q_39442172 values('id1', 'event_1', '20160789');
insert into q_39442172 values('id2', 'event_2', '20160689');
insert into q_39442172 values('id3', 'event_3', '20160679');
insert into q_39442172 values('id4', 'event_4', '20160579');
insert into q_39442172 values('id3', 'event_3', '20160579');
insert into q_39442172 values('id2', 'event_5', '20160379');
insert into q_39442172 values('id1', 'event_6', '20160339');


create or replace function query_event_sequence() returns table( r_id character varying, r_events text ) as
$$
declare
    vc_id            character varying;
    vc_event_id      character varying;
begin
    for ref_User in execute 'select distinct id from q_39442172 order by id' loop
        vc_id   := ref_User.id;
        r_id    := ref_User.id;

        for ref_Event in execute 'select event_id from q_39442172 where id = ' || vc_id loop
            vc_event_id := ref_Event.event_id;
            r_events    := concat_ws( ',', r_events, vc_event_id );
        end loop;

        raise notice '%: %', r_id, r_events;
        return next;
    end loop;
end;
$$
language plpgsql;

The exception i get:

NOTICE:  id1: event_6,event_1
ERROR:  cursor "<unnamed portal 2>" already in use
CONTEXT:  PL/pgSQL function query_event_sequence() line 13 at OPEN
********** Error **********

ERROR: cursor "<unnamed portal 2>" already in use
SQL state: 42P03

Actually, using array_agg can do what i want to do, but i am just confused about why nested cursor loop in my code won't work.

Why are you collecting all those IDs in a single big comma separated string? That doesn't make sense (at least to me). What are you trying to do with those IDs? — user330315
– user330315, Commented Sep 13, 2016 at 9:13
@a_horse_with_no_name I run this code just for pratice, and what i want to know is that how to use nested cursor loop in postgesql. — zhenjun huang
– zhenjun huang, Commented Sep 13, 2016 at 9:21
Your example is incomplete. ref_User and ref_Event are not declared anywhere. Could you post the complete code and the complete error message, please? — Laurenz Albe
– Laurenz Albe, Commented Sep 13, 2016 at 9:48
When I add the missing declarations for the records, this works for me. Where and how are you calling the function? — user330315
– user330315, Commented Sep 13, 2016 at 9:53
@LaurenzAlbe when using cursor with for loop, you do not need to declare cursor variable. This is complete code. — zhenjun huang
– zhenjun huang, Commented Sep 13, 2016 at 10:15

user330315 · Accepted Answer · 2016-09-13 09:10:20Z

1

You don't need a function or a cursor for this. A single SQL statement will do:

select string_agg(concat_ws(',', event_id, id), ',' order by id)
from q_39442172 
where id in (select id from q_39442172)

answered Sep 13, 2016 at 9:10

user330315

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

2 Comments

zhenjun huang Over a year ago

Thinks for your answer. String_agg does work, but why it won't work when using nested cursor loop in postgresql ?

zhenjun huang Over a year ago

What i want to know is how to make this code work, any suggestion?

Collectives™ on Stack Overflow

nested cursor loop in postgresql

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