Re: no_data_found oracle vs pg

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