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
