error while executing function with cursor

Question

I defined a function as following

CREATE OR REPLACE FUNCTION processActivityCommentTable() RETURNS INTEGER AS $$
DECLARE
activityCommentRow RECORD;
i RECORD;
activityId integer;
attachments text;
attachmentId integer;
cur1 CURSOR FOR SELECT ac.id, ac.attachments from activitycomment ac where ac.attachments is not null;

BEGIN
OPEN cur1;
FOR activityCommentRow in cur1
    LOOP
    RAISE NOTICE 'currently processing for %s ...', activityCommentRow.id;
        -- can do some processing here
    activityId = activityCommentRow.id;
    attachments = activityCommentRow.attachments;

    SELECT foo FROM regexp_split_to_table(attachments,E'{"id":') as foo;

    FOR i in select * from foo
    LOOP
     select regexp_replace(i,'(,"name").*','') into attachmentId;
        EXECUTE 'INSERT INTO attachment (activity_comment_id) values(' || attachmentId ||') where id= ' activityId; 
    END LOOP;

    END LOOP;
CLOSE cur1;
END;

$$ LANGUAGE plpgsql;

while executing it

select processActivityCommentTable();

it gives me following errors

ERROR: cursor "cur1" already in use SQL state: 42P03 Context: PL/pgSQL function processactivitycommenttable() line 12 at FOR over cursor

Can anyone please help?

Daniel Vérité · Accepted Answer · 2014-09-19 10:27:08Z

Short answer: put a query in the FOR loop, not a cursor.

The FOR loop is documented as:

[ label ]
FOR target IN query LOOP
statements
END LOOP [ label ];

Where query is described as:

The query used in this type of FOR statement can be any SQL command that returns rows to the caller: SELECT is the most common case, but you can also use INSERT, UPDATE, or DELETE with a RETURNING clause. Some utility commands such as EXPLAIN will work too.

It doesn't imply a cursor's name may be there.

You may give it the SQL query for the cursor rather than the cursor.

If a cursor really needs to be there, the command to read results from a cursor is FETCH, so this form would be accepted:

FOR activityCommentRow in FETCH ALL FROM cur1

or the variants of FETCH, for example if only 3 rows are needed:

FOR activityCommentRow in FETCH 3 FROM cur1

Pavel Stehule · Accepted Answer · 2014-09-20 05:22:12Z

0

When you use a FOR IN CURSOR statement, then you should not to open cursor explicitly -

The cursor variable must have been bound to some query when it was declared, and it cannot be open already. The FOR statement automatically opens the cursor, and it closes the cursor again when the loop exits.

Explicit opening is necessary only when you use a some generic form and you read a data from cursor by statement FETCH. Details are in doc.

answered Sep 20, 2014 at 5:22

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Collectives™ on Stack Overflow

error while executing function with cursor

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related