Date: Wed, 15 Dec 2021 12:36:44 +0100
From: hubert depesz lubaczewski <depesz@depesz.com>
Sender: depesz@depesz.com
To: James Kitambara <jameskitambara@yahoo.co.uk>
Cc: Sandeep Saxena <sandeep.lko@gmail.com>,
	"pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL
Message-ID: <20211215113644.GA26977@depesz.com>
Reply-To: depesz@depesz.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>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <615924257.1194226.1639150841188@mail.yahoo.com>
User-Agent: Mutt/1.10.1 (2018-07-13)
Archived-At: 
 <https://www.postgresql.org/message-id/20211215113644.GA26977%40depesz.com>
Precedence: bulk

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