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
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
CREATE TABLE public.customers AS select 1 as id, 'John Doe' as name;
Short story long, there's no problem with an OUT REFCURSOR parameter, the problem is with plain SQL offering no way of holding onto the resulting cursor. In PL/pgSQL CALL works a bit differently, so it's not an issue:
CREATE PROCEDURE customer_select(OUT customers REFCURSOR) AS $p$
BEGIN OPEN customers FOR SELECT * FROM public.customers;
END $p$ LANGUAGE plpgsql;
DO $p$
DECLARE customer_cursor refcursor;
customer_record record;
BEGIN CALL customer_select(customer_cursor);
FETCH NEXT FROM customer_cursor INTO customer_record;
CREATE TABLE fetched AS SELECT customer_record.id, customer_record.name;
END $p$;
SELECT * FROM fetched;
| id | name |
|---|---|
| 1 | John Doe |
PL/pgSQL CALL accepts a variable as an OUT parameter in order to overwrite it. Plain SQL CALL requires that you provide an argument of the right type (or coercible to the right type) only to match the signature; it doesn't do anything with it. If you wanted it to affect the routine, you'd make it an IN or INOUT. As a consequence, you could've just as well give it a null, which is the convention.
CREATE PROCEDURE customer_select(OUT customers REFCURSOR) AS $p$
BEGIN OPEN customers FOR SELECT * FROM public.customers;
END $p$ LANGUAGE plpgsql;
CALL customer_select(null);
Thing is, Internally, a refcursor value is simply the string name of the portal containing the active query for the cursor. So giving it a null (or making it ignore the value because it's an OUT parameter) leaves the cursor without a name, forcing PostgreSQL to use a default name similar to how unnamed columns in a select are named ?column? by default.
| customers |
|---|
| <unnamed portal 2> |
It is available in pg_cursors but there's no way to dynamically use it in plain SQL - you can't give fetch a subquery that returns the name. The operator (you manually or the app using the client) can only read it from the result of the CALL (or look it up) and feed it back into FETCH.
FETCH ALL FROM "<unnamed portal 2>";
| id | name |
|---|---|
| 1 | John Doe |
You can't override that default name either, because OUT parameters don't accept defaults:
CREATE PROCEDURE customer_select(OUT customers REFCURSOR default 'customer_cursor') AS $p$
BEGIN OPEN customers FOR SELECT * FROM public.customers;
END $p$ LANGUAGE plpgsql;
ERROR: only input parameters can have default values
INOUT of course solves the problem because while you're not really getting the cursor back, you can reference it by the name you gave it (without having the procedure quietly discard it), removing the need for a name lookup/rewrite.
CREATE PROCEDURE customer_select(INOUT customers REFCURSOR) AS $p$
BEGIN OPEN customers FOR SELECT * FROM public.customers;
END $p$ LANGUAGE plpgsql;
CALL customer_select('customer_cursor');
FETCH ALL FROM "customer_cursor";
| customers |
|---|
| customer_cursor |
| id | name |
|---|---|
| 1 | John Doe |
