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