Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pgY40-0002UM-5O for pgsql-hackers@arkaria.postgresql.org; Sun, 26 Mar 2023 21:38:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pgY3y-00062K-F8 for pgsql-hackers@arkaria.postgresql.org; Sun, 26 Mar 2023 21:38:06 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pgY3x-00062B-VU for pgsql-hackers@lists.postgresql.org; Sun, 26 Mar 2023 21:38:06 +0000 Received: from mail-oa1-x35.google.com ([2001:4860:4864:20::35]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pgY3v-0007GP-FF for pgsql-hackers@lists.postgresql.org; Sun, 26 Mar 2023 21:38:05 +0000 Received: by mail-oa1-x35.google.com with SMTP id 586e51a60fabf-17aaa51a911so7391564fac.5 for ; Sun, 26 Mar 2023 14:38:03 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1679866682; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=zvaqB5rY4YaP3ZDYqJrVikegqWLH+K5n2GK2Pgpos6Q=; b=UhUFyrAapEEfxIT99fmRu5v3NraQmjQIAeD07Hn9Hv1GKSGPfgyvFQkN8rdQQoymN9 DIi81cAK1VLux4PErzykOcpUC1wNXR1+E6llgF9tBmfAruK2mdtcWKq1CrQixfr/cdVB gZArz3blX/+UoeLH8Sl1/u4PmVrCPk4VXoAonnwjE7Qf4aG5tINMAfHlXThEZHX55VQ5 RsU9LizW6oeii69wz/RkFTlCDm26dbuaWOw8+NF/r4XCtZk1hFSg8px61tbpgk5QSdKQ cHbxy8bWkzrngQe7cCmYQFRdgo4eUh1opnVE33Sqdh0LqUyW/w327s2puC/dkL1pbt1E DxxA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1679866682; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=zvaqB5rY4YaP3ZDYqJrVikegqWLH+K5n2GK2Pgpos6Q=; b=H2iY5kRmqIlu/59xstfN/XoyhzXDz1/dgQwcP9VLoqLuvncxJgg/j8H+eXiqxWQgIb Zauw0EO5IZmZo6RLvlTGkR4EIlCNFIENjFVGwyIz+YtE6vGHDUXrctr8lyDou3Q7zbqN yQz/s2kN6M2hcH2BMatg741rxOs9/RDP1S8hh3de3ivoIzSDVoP3QIkZiHY0M8/4I7R9 5KCn1mD1SyZCsSVUbKvjuSryomXjgiZP1nSv99qiYBnDAfv9exaLXrcmOLlllI/uK9Pm OjdU4KQ9/hEQvBNPYrZFKNTr0mghSlk3TgsRrXqUgn44uh2Q195pUuBxv2Roe77K6NhE s78w== X-Gm-Message-State: AAQBX9eK8mA/4AA36B88vyB5+gX7Rg6Ix43ZyfbpCFXIdFHERSUfOgGS yDxvfjwO/HkWWfUHBZVEwcnI6h79LdEodGsVIvQ= X-Google-Smtp-Source: AKy350Y9OxyXLgElzsLKpXIzKi3ht1r/fuAprctjNVt50DVkJlFr1fJkeZxSrwO4yLXQRYbwKJzfU17vzwOQWI4igPk= X-Received: by 2002:a05:6871:e45:b0:17f:1723:fc82 with SMTP id vk5-20020a0568710e4500b0017f1723fc82mr937678oab.9.1679866682677; Sun, 26 Mar 2023 14:38:02 -0700 (PDT) MIME-Version: 1.0 References: <20230208063316.atjv6uxb7owm77s6@jrouhaud> In-Reply-To: From: Kirk Wolak Date: Sun, 26 Mar 2023 17:37:36 -0400 Message-ID: Subject: Re: possible proposal plpgsql GET DIAGNOSTICS oid = PG_ROUTINE_OID To: Pavel Stehule Cc: Julien Rouhaud , PostgreSQL Hackers Content-Type: multipart/alternative; boundary="000000000000e41dae05f7d470ec" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000e41dae05f7d470ec Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Feb 8, 2023 at 10:56=E2=80=AFAM Kirk Wolak wrote= : > On Wed, Feb 8, 2023 at 3:08 AM Pavel Stehule > wrote: > >> hi >> >> st 8. 2. 2023 v 7:33 odes=C3=ADlatel Julien Rouhaud >> napsal: >> >>> On Tue, Feb 07, 2023 at 08:48:22PM +0100, Pavel Stehule wrote: >>> > >>> > GET DIAGNOSTICS _oid =3D PG_ROUTINE_OID; >>> > RAISE NOTICE '... % ... %', _oid, _oid::regproc::text; >>> > >>> > 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 =3D pg_current_routine_signature, >> n =3D pg_current_routine_name, >> o =3D 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=3D# select foo(10); >> NOTICE: sign:foo(integer), name:foo, oid:16392 >> =E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=90 >> =E2=94=82 foo =E2=94=82 >> =E2=95=9E=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=A1 >> =E2=94=82 10 =E2=94=82 >> =E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=98 >> (1 row) >> >> The name - pg_routine_oid can be confusing, because there is not clean i= f >> it is oid of currently executed routine or routine from top of exception >> >> Regards >> >> Pavel >> > > I agree that the name changed to pg_current_routine_... makes the most > sense, great call... > > +1 > Okay, I reviewed this. I tested it (allocating too small of varchar's for values, various "signature types"), and also a performance test... Wow, on my VM, 10,000 Calls in a loop was 2-4ms... The names are clear. Again, I tested with various options, and including ROW_COUNT, or not. This functions PERFECTLY.... Except there are no documentation changes. Because of that, I set it to Waiting on Author. Which might be unfair, because I could take a stab at doing the documentation (but docs are not compiling on my setup yet). The documentation changes are simple enough. If I can get the docs compiled on my rig, I will see if I can make the changes, and post an updated patch, that contains both... But I don't want to be stepping on toes, or having it look like I am taking credit. Regards - Kirk --000000000000e41dae05f7d470ec Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Wed, Feb 8, 2023 at 10:56=E2=80=AFAM K= irk Wolak <wolakk@gmail.com> = wrote:
On Wed, Feb 8, 2023 at = 3:08 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:
hi

st 8. 2. 2023 v=C2=A07:33 odes=C3=ADlatel Julien Rouh= aud <rjuju123@gm= ail.com> napsal:
On Tue, Feb 07, 2023 at 08:48:22PM +0100, Pavel Stehule wrote:
&= gt;
> GET DIAGNOSTICS _oid =3D PG_ROUTINE_OID;
> RAISE NOTICE '... % ... %', _oid, _oid::regproc::text;
>
> 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

<= span style=3D"font-family:monospace">create or replace function foo(a int)<= br>returns int as $$
declare s text; n text; o oid;
begin
=C2=A0 g= et diagnostics s =3D pg_current_routine_signature,
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 n =3D pg_current_routine_name,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 o =3D pg_cu= rrent_routine_oid;
=C2=A0 raise notice 'sign:%, =C2=A0name:%, =C2=A0= oid:%', s, n, o;
=C2=A0 return a;
end;
$$ language plpgsql;CREATE FUNCTION
(2023-02-08 09:04:03) postgres=3D# select foo(10);
N= OTICE: =C2=A0sign:foo(integer), =C2=A0name:foo, =C2=A0oid:16392
=E2=94= =8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=90
=E2=94=82 foo = =E2=94=82
=E2=95=9E=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =A1
=E2=94=82 =C2=A010 =E2=94=82
=E2=94=94=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=98
(1 row)

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

Regards

Pavel

I agree that the name changed to pg= _current_routine_...=C2=A0 makes the most sense, great call...
+1=C2=A0=C2=A0=C2=A0

Okay, I reviewed this.=C2=A0 I tested it (allocating too small of = varchar's=C2=A0for=C2=A0values, various "signature types"),
and also a performance test... Wow, on my VM, 10,000 Calls in a lo= op was 2-4ms...

The names are clear.=C2=A0 Again, = I tested with various options, and including ROW_COUNT, or not.
<= br>
This functions PERFECTLY....=C2=A0 Except there are no docume= ntation changes.
Because of that, I set it to Waiting on Author.= =C2=A0=C2=A0
Which might be unfair, because I could take a stab a= t doing the documentation (but docs are not compiling on my setup yet).

The documentation changes are simple enough.
If I can get the docs compiled on my rig, I will see if I can make the ch= anges, and post an updated patch,
that contains both...

But I don't want to be stepping on toes, or having it l= ook like I am taking credit.

Regards - Kirk
<= /div>
--000000000000e41dae05f7d470ec--