Re: ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL

On Fri, 10 Dec 2021 at 15:40, James Kitambara
<jameskitambara@yahoo.co.uk> wrote:
>
> There is no COMMIT in the loop for processing cursor data.
>
> Sorry I forget to share the procedure on my first email:
>
> Here is a procedure:
> -------------------------------------------------------
>
> CREATE OR REPLACE PROCEDURE public.temp_insert_in_books2(
> )
> LANGUAGE 'edbspl'
>     SECURITY DEFINER VOLATILE PARALLEL UNSAFE
>     COST 100
> AS $BODY$
>     --v_id         INTEGER;
>     v_title      CHAR(10);
> v_amount NUMERIC;
>     CURSOR book_cur IS
>         SELECT title, amount FROM books2 WHERE id >=8;
> BEGIN
>     OPEN book_cur;
>     LOOP
>         FETCH book_cur INTO v_title, v_amount;
>         EXIT WHEN book_cur%NOTFOUND;
> INSERT INTO books2 (title, amount) VALUES (v_title, v_amount);
>     END LOOP;
> COMMIT;
>     CLOSE book_cur;
> END
> $BODY$;

"Normally, cursors are automatically closed at transaction commit."
https://www.postgresql.org/docs/devel/plpgsql-transactions.html

So the explicit CLOSE is not needed, if you have the COMMIT.

But then why have the COMMIT? The transaction will commit by default,
so I would remove that.

-- 
Simon Riggs                http://www.EnterpriseDB.com/