public inbox for [email protected]  

help / color / mirror / Atom feed

From: PetSerAl <[email protected]>
To: [email protected]
Subject: Get cursor name for invalid_cursor_name error
Date: Mon, 7 Oct 2024 18:15:48 +0300
Message-ID: <CAKygsHT0dE+UNGR74_4dJrix9YTidPLGMu2GYj6ZBXMvzCc0sQ@mail.gmail.com> (raw)

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
------------------------

(1 row)


postgres=# BEGIN;
BEGIN
postgres=*# DECLARE "fgh" SCROLL CURSOR FOR VALUES
(value_from_cursor_unsafe('jkl'));
DECLARE CURSOR
postgres=*# SELECT value_from_cursor_safe('fgh'); -- case 2
INFO:  cursor "jkl" does not exist
 value_from_cursor_safe
------------------------

(1 row)


postgres=*# COMMIT;
COMMIT

For example, in given example in "case 2" I want to rethrow error,
because it is not about 'fgh' cursor, which I directly query. But it
seems cursor name only available as part of localizable error message,
but not as separate field through GET STACKED DIAGNOSTICS.

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]
  Subject: Re: Get cursor name for invalid_cursor_name error
  In-Reply-To: <CAKygsHT0dE+UNGR74_4dJrix9YTidPLGMu2GYj6ZBXMvzCc0sQ@mail.gmail.com>

* 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