From: | Simon Riggs <simon(dot)riggs(at)enterprisedb(dot)com> |
---|---|
To: | James Kitambara <jameskitambara(at)yahoo(dot)co(dot)uk> |
Cc: | Sandeep Saxena <sandeep(dot)lko(at)gmail(dot)com>, "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL |
Date: | 2021-12-14 10:58:37 |
Message-ID: | CANbhV-EWz14UTCZnJphL+o6b=aWvLpLFQi+jfFNb=bqfQBrBLg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Fri, 10 Dec 2021 at 15:40, James Kitambara
<jameskitambara(at)yahoo(dot)co(dot)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/
From | Date | Subject | |
---|---|---|---|
Next Message | James Kitambara | 2021-12-14 15:22:03 | Re: ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL |
Previous Message | Steve Midgley | 2021-12-14 04:19:09 | Re: 5 tables with 3 different relation cases |