Re: Get cursor name for invalid_cursor_name error

On 10/7/24 08:15, PetSerAl wrote:
> How to reliable get cursor name which cause invalid_cursor_name error?
> 
> postgres=# CREATE FUNCTION value_from_cursor_unsafe(cursor_name text)
> RETURNS integer
> postgres-# STRICT LANGUAGE plpgsql
> postgres-# AS $$
> postgres$#   DECLARE
> postgres$#     cursor CONSTANT refcursor NOT NULL := cursor_name;
> postgres$#     result integer;
> postgres$#   BEGIN
> postgres$#     FETCH FIRST FROM cursor INTO result;
> postgres$#     return result;
> postgres$#   END
> postgres$# $$;
> CREATE FUNCTION
> postgres=# CREATE FUNCTION value_from_cursor_safe(cursor_name text)
> RETURNS integer
> postgres-# STRICT LANGUAGE plpgsql
> postgres-# AS $$
> postgres$#   DECLARE
> postgres$#     result integer;
> postgres$#   BEGIN
> postgres$#     BEGIN
> postgres$#       result := value_from_cursor_unsafe(cursor_name);
> postgres$#     EXCEPTION
> postgres$#       WHEN invalid_cursor_name THEN
> postgres$#         RAISE INFO '%', SQLERRM;
> postgres$#     END;
> postgres$#     return result;
> postgres$#   END
> postgres$# $$;
> CREATE FUNCTION
> postgres=# SELECT value_from_cursor_safe('asd'); -- case 1
> INFO:  cursor "asd" does not exist
>   value_from_cursor_safe
> ------------------------

FYI, if you do \ef value_from_cursor_unsafe the function definition will 
appear in an editor and from there you can copy and paste to make the 
output easier to follow.



-- 
Adrian Klaver
adrian.klaver@aklaver.com