public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: PetSerAl <[email protected]>
To: [email protected]
Subject: Re: Get cursor name for invalid_cursor_name error
Date: Mon, 7 Oct 2024 08:27:28 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKygsHT0dE+UNGR74_4dJrix9YTidPLGMu2GYj6ZBXMvzCc0sQ@mail.gmail.com>
References: <CAKygsHT0dE+UNGR74_4dJrix9YTidPLGMu2GYj6ZBXMvzCc0sQ@mail.gmail.com>

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
[email protected]

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Get cursor name for invalid_cursor_name error
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox