Feedback-ID: i76984098:Fastmail
Message-ID: <72db04f3-d064-44f6-9a55-dfdcb88b0937@aklaver.com>
Date: Mon, 7 Oct 2024 08:27:28 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Get cursor name for invalid_cursor_name error
To: PetSerAl <petseral@gmail.com>, pgsql-general@lists.postgresql.org
References: <CAKygsHT0dE+UNGR74_4dJrix9YTidPLGMu2GYj6ZBXMvzCc0sQ@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAKygsHT0dE+UNGR74_4dJrix9YTidPLGMu2GYj6ZBXMvzCc0sQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/72db04f3-d064-44f6-9a55-dfdcb88b0937%40aklaver.com>
Precedence: bulk

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