agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: hubert depesz lubaczewski <depesz@depesz.com>
To: James Kitambara <jameskitambara@yahoo.co.uk>
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: Wed, 15 Dec 2021 12:36:44 +0100
Message-ID: <20211215113644.GA26977@depesz.com> (raw)
In-Reply-To: <615924257.1194226.1639150841188@mail.yahoo.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>
On Fri, Dec 10, 2021 at 03:40:41PM +0000, James Kitambara 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$;
Hi,
others helped you with the reported problem, but I'd like to point out
that your procedure is doing a job, and it's doing it poorly (slowly).
There is no need for iteration. There is no need for cursor. There is
even no need for procedure, but let's keep it there.
Your whole procedure can be simplified to:
CREATE OR REPLACE PROCEDURE public.temp_insert_in_books2()
LANGUAGE 'plpgsql'
SECURITY DEFINER VOLATILE PARALLEL UNSAFE
COST 100
AS $BODY$
DECLARE
BEGIN
INSERT INTO books2 (title, amount)
SELECT title, amount FROM books2 where id >= 8;
END
$BODY$;
And that's it.
It will be faster (single insert, instead of one-for-each-row), and
definitely easier to read and maintain.
Best regards,
depesz
view thread (6+ messages)
Message-ID: <20211215113644.GA26977@depesz.com>
Permalink: ../20211215113644.GA26977@depesz.com/
Also on: postgresql.org/message-id/20211215113644.GA26977@depesz.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: depesz@depesz.com, jameskitambara@yahoo.co.uk, sandeep.lko@gmail.com
Subject: Re: ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL
In-Reply-To: <20211215113644.GA26977@depesz.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