agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: James Kitambara <jameskitambara@yahoo.co.uk>
To: Simon Riggs <simon.riggs@enterprisedb.com>
Cc: Sandeep Saxena <sandeep.lko@gmail.com>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL
Date: Tue, 14 Dec 2021 15:22:03 +0000 (UTC)
Message-ID: <1815841634.427016.1639495323025@mail.yahoo.com> (raw)
In-Reply-To: <CANbhV-EWz14UTCZnJphL+o6b=aWvLpLFQi+jfFNb=bqfQBrBLg@mail.gmail.com>
References: <820139578.307641.1639046195013.ref@mail.yahoo.com>
<820139578.307641.1639046195013@mail.yahoo.com>
<CAA3fAREzZF1J5hoBO+YmmVcvTe7Jdd9O3S9Mrk=TzTFXew_dHw@mail.gmail.com>
<615924257.1194226.1639150841188@mail.yahoo.com>
<CANbhV-EWz14UTCZnJphL+o6b=aWvLpLFQi+jfFNb=bqfQBrBLg@mail.gmail.com>
Thank you Mr. Simon for your comment I will try that and see the results.
Kindly Regards,James Kitambara
On Tuesday, 14 December 2021, 13:59:01 GMT+3, Simon Riggs <simon.riggs@enterprisedb.com> wrote:
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/
view thread (6+ messages) latest in thread
Message-ID: <1815841634.427016.1639495323025@mail.yahoo.com>
Permalink: ../1815841634.427016.1639495323025@mail.yahoo.com/
Also on: postgresql.org/message-id/1815841634.427016.1639495323025@mail.yahoo.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jameskitambara@yahoo.co.uk, simon.riggs@enterprisedb.com, sandeep.lko@gmail.com
Subject: Re: ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL
In-Reply-To: <1815841634.427016.1639495323025@mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox