Postgres Pro
Downloads
Home   >   mailing lists

Re: ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL - Mailing list pgsql-sql

From James Kitambara
Subject Re: ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL
Date
Msg-id 615924257.1194226.1639150841188@mail.yahoo.com
Whole thread Raw
In response to Re: ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL  (Sandeep Saxena <sandeep.lko@gmail.com>)
Responses Re: ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL
Re: ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL
List pgsql-sql
Tree view
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$;




On Thursday, 9 December 2021, 13:55:31 GMT+3, Sandeep Saxena <sandeep.lko@gmail.com> wrote:


Do you have commit inside cursor?

On Thu, Dec 9, 2021 at 4:06 PM James Kitambara <jameskitambara@yahoo.co.uk> wrote:

ISSUE OF CURSOR ON THE EDB POSTGRESQL

I have the table books2 below with those fields on EDB PostgreSQL.

CREATE TABLE IF NOT EXISTS public.books2
(

    id integer NOT NULL DEFAULT nextval('books2_id_seq'::regclass),

    title character(10) COLLATE pg_catalog."default" NOT NULL,

    amount numeric DEFAULT 0,

    CONSTRAINT books2_pkey PRIMARY KEY (id)

);

 

The table is populated with the following data

Inline image

 

I want to re-insert the records from ID 8 to 11  for the values of TITLE and AMOUNT as the ID is out-increment. To accomplish this I have created the procedure named temp_insert_in_books2() to do this

The procedure does what I wanted BUT IT GIVES ME THIS ERROR MESSAGE:

ERROR:  cursor "book_cur" does not exist

CONTEXT:  edb-spl function temp_insert_in_books2() line 15 at CLOSE

SQL state: 34000

HOW CAN I REMOVE THAT ERROR?. ALSO NOTE THAT I ALWAYS GET THIS ERROR WHEN UPDATING OR INSERTING DATA ON THE TABLE USING CURSORS.

PLEASE CAN ANYONE ASSIST.

 

Table Data after running the procedure is described below:

Inline image


Attachment

pgsql-sql by date:

Previous
From: Sandeep Saxena
Date:
Subject: Re: ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL
Next
From: Brice André
Date:
Subject: Memory exhaustion on large query