public inbox for [email protected]  

help / color / mirror / Atom feed

From: David Rowley <[email protected]>
To: Jean-Marc Voillequin (MA) <[email protected]>
Cc: Pavel Stehule <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: no_data_found oracle vs pg
Date: Tue, 19 Sep 2023 01:13:40 +1200
Message-ID: <CAApHDvrhBh9PwphEUH973mtKjqT5MBN3tPB5sJmKj8G7ZtjWVA@mail.gmail.com> (raw)
In-Reply-To: <MN2PR20MB27353A509944FDE44CBF5E5DBEFBA@MN2PR20MB2735.namprd20.prod.outlook.com>
References: <MN2PR20MB273509335B549DEB90AB5724BEF5A@MN2PR20MB2735.namprd20.prod.outlook.com>
	<CAFj8pRAfJy7du1O5fkCbNUPJticxrQpomu7mcPYs2vJNfEKkJw@mail.gmail.com>
	<MN2PR20MB27353A509944FDE44CBF5E5DBEFBA@MN2PR20MB2735.namprd20.prod.outlook.com>

On Mon, 18 Sept 2023 at 18:49, Jean-Marc Voillequin (MA)
<[email protected]> wrote:
> I know I can test the ROWCOUNT or the FOUND indicator, but it’s not what I want.
>
> I want a NO_DATA_FOUND exception to be raised when the function is called from a PL/pgSQL block, and I want the function to return a NULL value when called from SQL.

It would mean having to include logic in each function, but perhaps
GET DIAGNOSTIC PG_CONTEXT could be of some use.

You could adapt the following to call the STRICT or non-STRICT version
accordingly.

create or replace function myfunc() returns int as $$
declare ctx text;
begin
GET DIAGNOSTICS ctx = PG_CONTEXT;
if split_part(ctx, E'\n', 2) = '' then
raise notice 'top level';
else
raise notice 'nested';
end if;
return 1;
end;
$$ language plpgsql;

create or replace function callerfunc() returns int as $$
begin
return myfunc();
end;
$$ language plpgsql;


select myfunc();
select callerfunc();

David

view thread (6+ messages)  latest in thread

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: no_data_found oracle vs pg
  In-Reply-To: <CAApHDvrhBh9PwphEUH973mtKjqT5MBN3tPB5sJmKj8G7ZtjWVA@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