Re: possible proposal plpgsql GET DIAGNOSTICS oid = PG_ROUTINE_OID

hi

st 8. 2. 2023 v 7:33 odesílatel Julien Rouhaud <rjuju123(at)gmail(dot)com> napsal:

> On Tue, Feb 07, 2023 at 08:48:22PM +0100, Pavel Stehule wrote:
> >
> > I have a question about the possibility of simply getting the name of the
> > currently executed function. The reason for this request is
> simplification
> > of writing debug messages.
> >
> > GET DIAGNOSTICS _oid = PG_ROUTINE_OID;
> > RAISE NOTICE '... % ... %', _oid, _oid::regproc::text;
> >
> > The advantage of this dynamic access to function name is always valid
> value
> > not sensitive to some renaming or moving between schemas.
> >
> > I am able to separate a name from context, but it can be harder to write
> > this separation really robustly. It can be very easy to enhance the GET
> > DIAGNOSTICS statement to return the oid of currently executed function.
> >
> > Do you think it can be useful feature?
>
> +1, it would have been quite handy in a few of my projects.
>

it can looks like that

create or replace function foo(a int)
returns int as $$
declare s text; n text; o oid;
begin
get diagnostics s = pg_current_routine_signature,
n = pg_current_routine_name,
o = pg_current_routine_oid;
raise notice 'sign:%, name:%, oid:%', s, n, o;
return a;
end;
$$ language plpgsql;
CREATE FUNCTION
(2023-02-08 09:04:03) postgres=# select foo(10);
NOTICE: sign:foo(integer), name:foo, oid:16392
┌─────┐
│ foo │
╞═════╡
│ 10 │
└─────┘
(1 row)

The name - pg_routine_oid can be confusing, because there is not clean if
it is oid of currently executed routine or routine from top of exception

Regards

Pavel