Question 270117 in PostgreSQL Question posted in
The official documentation can be found here.

cursor does not exist in postgreSQL stored procedure

here is my stored proc in PostgreSQL version 15:

CREATE OR REPLACE PROCEDURE customer_select(OUT customers REFCURSOR)
LANGUAGE plpgsql
AS $$
BEGIN
    OPEN customers FOR
    SELECT * FROM public.customers;
END;
$$;

and here I am trying to call the proc

CALL customer_select('customer_cursor');
FETCH ALL FROM customer_cursor;

But I am getting an error

ERROR:  cursor "customer_cursor" does not exist

How do I get this to work? thanks

Tags:

2

Answers


  1. 0

    You’re pretty close, I think the main issue is that you’re using an OUT parameter rather than an INOUT parameter.

    What you currently have returns something like:

    <unnamed portal 13>

    Meaning you’d have to do fetch all from "<unnamed portal 13>";

    Eg: you aren’t assigning it a name and a auto-generated name is being assigned instead. You could find this from a catalog (pg_cursors) table, but it’s better to explicitly name it.

    If you change the parameter to be customers INOUT REFCURSOR then you get something like:

    call customer_select('my_cursor');

> my_cursor

    Meaning you can do:

    begin;
call customer_select('my_cursor');
fetch all from my_cursor;
commit
    Login or Signup to reply.
  2. 0

    Because the cursor was not declared before attempting to fetch from it in your PostgreSQL stored method, the error cursor 'customer_cursor' does not exist appears. Explicitly declaring the cursor before using the stored method will help you fix this problem. The cursor can be declared and linked to the output of the customer_select stored procedure using the DECLARE command. Once the stored procedure has been called, the result will be linked to the declared cursor even if the cursor name is not specified. Finally, it is possible to retrieve data from the cursor without mentioning its name in the FETCH statement. With this revised method, you may access the data as intended while also ensuring that the cursor is correctly specified and that the error is removed.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search