MIME-Version: 1.0
References: 
 <MN2PR20MB273509335B549DEB90AB5724BEF5A@MN2PR20MB2735.namprd20.prod.outlook.com>
 <CAFj8pRAfJy7du1O5fkCbNUPJticxrQpomu7mcPYs2vJNfEKkJw@mail.gmail.com>
 <MN2PR20MB27353A509944FDE44CBF5E5DBEFBA@MN2PR20MB2735.namprd20.prod.outlook.com>
In-Reply-To: 
 <MN2PR20MB27353A509944FDE44CBF5E5DBEFBA@MN2PR20MB2735.namprd20.prod.outlook.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Tue, 19 Sep 2023 01:13:40 +1200
Message-ID: 
 <CAApHDvrhBh9PwphEUH973mtKjqT5MBN3tPB5sJmKj8G7ZtjWVA@mail.gmail.com>
Subject: Re: no_data_found oracle vs pg
To: "Jean-Marc Voillequin (MA)" <Jean-Marc.Voillequin@moodys.com>
Cc: Pavel Stehule <pavel.stehule@gmail.com>,
	"pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAApHDvrhBh9PwphEUH973mtKjqT5MBN3tPB5sJmKj8G7ZtjWVA%40mail.gmail.com>
Precedence: bulk

On Mon, 18 Sept 2023 at 18:49, Jean-Marc Voillequin (MA)
<Jean-Marc.Voillequin@moodys.com> wrote:
> I know I can test the ROWCOUNT or the FOUND indicator, but it=E2=80=99s n=
ot 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 =3D PG_CONTEXT;
if split_part(ctx, E'\n', 2) =3D '' 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