From: | James Kitambara <jameskitambara(at)yahoo(dot)co(dot)uk> |
---|---|
To: | Sandeep Saxena <sandeep(dot)lko(at)gmail(dot)com> |
Cc: | "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-10 15:40:41 |
Message-ID: | 615924257.1194226.1639150841188@mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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 100AS $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(dot)lko(at)gmail(dot)com> wrote:
Do you have commit inside cursor?
On Thu, Dec 9, 2021 at 4:06 PM James Kitambara <jameskitambara(at)yahoo(dot)co(dot)uk> wrote:
ISSUE OF CURSOR ON THE EDB POSTGRESQL
I have the table books2 below with those fields on EDBPostgreSQL.
CREATE TABLE IF NOT EXISTSpublic.books2
(
id integer NOT NULL DEFAULTnextval('books2_id_seq'::regclass),
title character(10) COLLATEpg_catalog."default" NOT NULL,
amount numeric DEFAULT 0,
CONSTRAINT books2_pkey PRIMARY KEY (id)
);
The table is populated with the following data
I want to re-insert the records from ID 8 to 11 for the values of TITLE and AMOUNT as the IDis 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() line15 at CLOSE
SQL state: 34000
HOW CAN I REMOVE THATERROR?. ALSO NOTE THAT I ALWAYS GET THIS ERROR WHEN UPDATING OR INSERTING DATA ONTHE TABLE USING CURSORS.
PLEASE CAN ANYONE ASSIST.
Table Data after running the procedure is described below:
Attachment | Content-Type | Size |
---|---|---|
![]() |
image/jpeg | 31.8 KB |
![]() |
image/jpeg | 23.8 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Brice André | 2021-12-12 18:25:49 | Memory exhaustion on large query |
Previous Message | Sandeep Saxena | 2021-12-09 10:55:10 | Re: ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL |