possible proposal plpgsql GET DIAGNOSTICS oid = PG_ROUTINE_OID

Started by Pavel Stehulealmost 3 years ago14 messages

pavel.stehule@gmail.com

almost 3 years ago

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?

The implementation should be trivial.

Comments, notes?

Regards

Pavel

Kirk Wolak

wolakk@gmail.com

almost 3 years ago

In reply to: Pavel Stehule (#1)

Re: possible proposal plpgsql GET DIAGNOSTICS oid = PG_ROUTINE_OID

On Tue, Feb 7, 2023 at 2:49 PM Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hi

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?

I was hoping it could be a CONSTANT like TG_OP (so the extra GET
DIAGNOSTICS wasn't invoked, but I have no idea the weight of that CODE
CHANGE)

Regardless, this concept is what we are looking for. We prefer to leave
some debugging scaffolding in our DB Procedures, but disable it by default.
We are looking for a way to add something like this as a filter on the
level of output.

Our Current USE CASE is
CALL LOGGING('Msg'); -- And by default nothing happens, unless we set
some session variables appropriately

We are looking for
CALL LOGGING('Msg', __PG_ROUTINE_OID ); -- Now we can enable logging by
the routine we are interested in!

The LOGGING routine currently checks a session variable to see if logging
is EVEN Desired, if not it exits (eg PRODUCTION).

Now we can add a single line check, if p_funcoid is IN my list of routines
I am debugging, send the output.

I will gladly work on the documentation side to help this happen!

+10

Show quoted text

The implementation should be trivial.

Comments, notes?

Regards

Pavel

Julien Rouhaud

rjuju123@gmail.com

almost 3 years ago

In reply to: Pavel Stehule (#1)

Re: possible proposal plpgsql GET DIAGNOSTICS oid = PG_ROUTINE_OID

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.

Pavel Stehule

pavel.stehule@gmail.com

almost 3 years ago

In reply to: Julien Rouhaud (#3)

1 attachment(s)

Re: possible proposal plpgsql GET DIAGNOSTICS oid = PG_ROUTINE_OID

st 8. 2. 2023 v 7:33 odesílatel Julien Rouhaud <rjuju123@gmail.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

Attachments:

plpgsql-get-diagnostics-routine-oid.patchtext/x-patch; charset=US-ASCII; name=plpgsql-get-diagnostics-routine-oid.patchDownload

diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index 70a002a0f6..908022a88a 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -2475,6 +2475,28 @@ exec_stmt_getdiag(PLpgSQL_execstate *estate, PLpgSQL_stmt_getdiag *stmt)
 				}
 				break;
 
+			case PLPGSQL_GETDIAG_CURRENT_ROUTINE_NAME:
+				{
+					char   *funcname;
+
+					funcname = get_func_name(estate->func->fn_oid);
+					exec_assign_c_string(estate, var, funcname);
+					if (funcname)
+						pfree(funcname);
+				}
+				break;
+
+			case PLPGSQL_GETDIAG_CURRENT_ROUTINE_OID:
+				exec_assign_value(estate, var,
+								  ObjectIdGetDatum(estate->func->fn_oid),
+								  false, OIDOID, -1);
+				  break;
+
+			case PLPGSQL_GETDIAG_CURRENT_ROUTINE_SIGNATURE:
+				exec_assign_c_string(estate, var,
+									 estate->func->fn_signature);
+				break;
+
 			default:
 				elog(ERROR, "unrecognized diagnostic item kind: %d",
 					 diag_item->kind);
diff --git a/src/pl/plpgsql/src/pl_funcs.c b/src/pl/plpgsql/src/pl_funcs.c
index 5a6eadccd5..bdf02f36cc 100644
--- a/src/pl/plpgsql/src/pl_funcs.c
+++ b/src/pl/plpgsql/src/pl_funcs.c
@@ -325,6 +325,12 @@ plpgsql_getdiag_kindname(PLpgSQL_getdiag_kind kind)
 			return "TABLE_NAME";
 		case PLPGSQL_GETDIAG_SCHEMA_NAME:
 			return "SCHEMA_NAME";
+		case PLPGSQL_GETDIAG_CURRENT_ROUTINE_NAME:
+			return "PG_CURRENT_ROUTINE_NAME";
+		case PLPGSQL_GETDIAG_CURRENT_ROUTINE_OID:
+			return "PG_CURRENT_ROUTINE_OID";
+		case PLPGSQL_GETDIAG_CURRENT_ROUTINE_SIGNATURE:
+			return "PG_CURRENT_ROUTINE_SIGNATURE";
 	}
 
 	return "unknown";
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index edeb72c380..9ea21fec3d 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -318,6 +318,9 @@ static	void			check_raise_parameters(PLpgSQL_stmt_raise *stmt);
 %token <keyword>	K_OR
 %token <keyword>	K_PERFORM
 %token <keyword>	K_PG_CONTEXT
+%token <keyword>	K_PG_CURRENT_ROUTINE_NAME
+%token <keyword>	K_PG_CURRENT_ROUTINE_OID
+%token <keyword>	K_PG_CURRENT_ROUTINE_SIGNATURE
 %token <keyword>	K_PG_DATATYPE_NAME
 %token <keyword>	K_PG_EXCEPTION_CONTEXT
 %token <keyword>	K_PG_EXCEPTION_DETAIL
@@ -1035,6 +1038,9 @@ stmt_getdiag	: K_GET getdiag_area_opt K_DIAGNOSTICS getdiag_list ';'
 									break;
 								/* these fields are allowed in either case */
 								case PLPGSQL_GETDIAG_CONTEXT:
+								case PLPGSQL_GETDIAG_CURRENT_ROUTINE_NAME:
+								case PLPGSQL_GETDIAG_CURRENT_ROUTINE_OID:
+								case PLPGSQL_GETDIAG_CURRENT_ROUTINE_SIGNATURE:
 									break;
 								default:
 									elog(ERROR, "unrecognized diagnostic item kind: %d",
@@ -1123,6 +1129,15 @@ getdiag_item :
 						else if (tok_is_keyword(tok, &yylval,
 												K_RETURNED_SQLSTATE, "returned_sqlstate"))
 							$$ = PLPGSQL_GETDIAG_RETURNED_SQLSTATE;
+						else if (tok_is_keyword(tok, &yylval,
+												K_PG_CURRENT_ROUTINE_NAME, "pg_current_routine_name"))
+							$$ = PLPGSQL_GETDIAG_CURRENT_ROUTINE_NAME;
+						else if (tok_is_keyword(tok, &yylval,
+												K_PG_CURRENT_ROUTINE_OID, "pg_current_routine_oid"))
+							$$ = PLPGSQL_GETDIAG_CURRENT_ROUTINE_OID;
+						else if (tok_is_keyword(tok, &yylval,
+												K_PG_CURRENT_ROUTINE_SIGNATURE, "pg_current_routine_signature"))
+							$$ = PLPGSQL_GETDIAG_CURRENT_ROUTINE_SIGNATURE;
 						else
 							yyerror("unrecognized GET DIAGNOSTICS item");
 					}
@@ -2523,6 +2538,9 @@ unreserved_keyword	:
 				| K_OPEN
 				| K_OPTION
 				| K_PERFORM
+				| K_PG_CURRENT_ROUTINE_NAME
+				| K_PG_CURRENT_ROUTINE_OID
+				| K_PG_CURRENT_ROUTINE_SIGNATURE
 				| K_PG_CONTEXT
 				| K_PG_DATATYPE_NAME
 				| K_PG_EXCEPTION_CONTEXT
diff --git a/src/pl/plpgsql/src/pl_unreserved_kwlist.h b/src/pl/plpgsql/src/pl_unreserved_kwlist.h
index 466bdc7a20..6d5a14eb28 100644
--- a/src/pl/plpgsql/src/pl_unreserved_kwlist.h
+++ b/src/pl/plpgsql/src/pl_unreserved_kwlist.h
@@ -81,6 +81,9 @@ PG_KEYWORD("open", K_OPEN)
 PG_KEYWORD("option", K_OPTION)
 PG_KEYWORD("perform", K_PERFORM)
 PG_KEYWORD("pg_context", K_PG_CONTEXT)
+PG_KEYWORD("pg_current_routine_name", K_PG_CURRENT_ROUTINE_NAME)
+PG_KEYWORD("pg_current_routine_oid", K_PG_CURRENT_ROUTINE_OID)
+PG_KEYWORD("pg_current_routine_signature", K_PG_CURRENT_ROUTINE_SIGNATURE)
 PG_KEYWORD("pg_datatype_name", K_PG_DATATYPE_NAME)
 PG_KEYWORD("pg_exception_context", K_PG_EXCEPTION_CONTEXT)
 PG_KEYWORD("pg_exception_detail", K_PG_EXCEPTION_DETAIL)
diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h
index 355c9f678d..42c094fb48 100644
--- a/src/pl/plpgsql/src/plpgsql.h
+++ b/src/pl/plpgsql/src/plpgsql.h
@@ -157,7 +157,10 @@ typedef enum PLpgSQL_getdiag_kind
 	PLPGSQL_GETDIAG_DATATYPE_NAME,
 	PLPGSQL_GETDIAG_MESSAGE_TEXT,
 	PLPGSQL_GETDIAG_TABLE_NAME,
-	PLPGSQL_GETDIAG_SCHEMA_NAME
+	PLPGSQL_GETDIAG_SCHEMA_NAME,
+	PLPGSQL_GETDIAG_CURRENT_ROUTINE_NAME,
+	PLPGSQL_GETDIAG_CURRENT_ROUTINE_OID,
+	PLPGSQL_GETDIAG_CURRENT_ROUTINE_SIGNATURE
 } PLpgSQL_getdiag_kind;
 
 /*

Kirk Wolak

wolakk@gmail.com

almost 3 years ago

In reply to: Pavel Stehule (#4)

Re: possible proposal plpgsql GET DIAGNOSTICS oid = PG_ROUTINE_OID

On Wed, Feb 8, 2023 at 3:08 AM Pavel Stehule <pavel.stehule@gmail.com>
wrote:

hi

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

On Tue, Feb 07, 2023 at 08:48:22PM +0100, Pavel Stehule wrote:

GET DIAGNOSTICS _oid = 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 = 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

I agree that the name changed to pg_current_routine_... makes the most
sense, great call...

Kirk Wolak

wolakk@gmail.com

almost 3 years ago

In reply to: Kirk Wolak (#5)

Re: possible proposal plpgsql GET DIAGNOSTICS oid = PG_ROUTINE_OID

On Wed, Feb 8, 2023 at 10:56 AM Kirk 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 7:33 odesílatel Julien Rouhaud <rjuju123@gmail.com>
napsal:

On Tue, Feb 07, 2023 at 08:48:22PM +0100, Pavel Stehule wrote:

GET DIAGNOSTICS _oid = 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 = 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

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

Kirk Wolak

wolakk@gmail.com

almost 3 years ago

In reply to: Kirk Wolak (#6)

1 attachment(s)

Re: possible proposal plpgsql GET DIAGNOSTICS oid = PG_ROUTINE_OID

On Sun, Mar 26, 2023 at 5:37 PM Kirk Wolak <wolakk@gmail.com> wrote:

On Wed, Feb 8, 2023 at 10:56 AM Kirk 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 7:33 odesílatel Julien Rouhaud <rjuju123@gmail.com>
napsal:

On Tue, Feb 07, 2023 at 08:48:22PM +0100, Pavel Stehule wrote:

GET DIAGNOSTICS _oid = 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 = 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

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

Okay, I have modified the documentation and made sure it compiles. They
were simple enough changes.
I am attaching this updated patch.

I have marked the item Ready for Commiter...

Thanks for your patience. I now have a workable hacking environment!

Regards - Kirk

Attachments:

0002-plpgsql-get-diagnostics-routine-oid.patchtext/plain; charset=US-ASCII; name=0002-plpgsql-get-diagnostics-routine-oid.patchDownload

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 7c8a49fe43..19dfe529cf 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -1639,6 +1639,21 @@ GET DIAGNOSTICS integer_var = ROW_COUNT;
          <entry>line(s) of text describing the current call stack
           (see <xref linkend="plpgsql-call-stack"/>)</entry>
         </row>
+       <row>
+         <entry><literal>PG_CURRENT_ROUTINE_SIGNATURE</literal></entry>
+         <entry><type>text</type></entry>
+         <entry>text describing the current routine with paramater types</entry>
+        </row>
+        <row>
+         <entry><literal>PG_CURRENT_ROUTINE_NAME</literal></entry>
+         <entry><type>text</type></entry>
+         <entry>text name of the function without parenthesis</entry>
+        </row>
+        <row>
+         <entry><literal>PG_CURRENT_ROUTINE_OID</literal></entry>
+         <entry><type>oid</type></entry>
+         <entry>oid of the function currently running</entry>
+        </row>
        </tbody>
       </tgroup>
      </table>
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index b0a2cac227..bb2f3ff828 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -2475,6 +2475,28 @@ exec_stmt_getdiag(PLpgSQL_execstate *estate, PLpgSQL_stmt_getdiag *stmt)
 				}
 				break;
 
+			case PLPGSQL_GETDIAG_CURRENT_ROUTINE_NAME:
+				{
+					char   *funcname;
+
+					funcname = get_func_name(estate->func->fn_oid);
+					exec_assign_c_string(estate, var, funcname);
+					if (funcname)
+						pfree(funcname);
+				}
+				break;
+
+			case PLPGSQL_GETDIAG_CURRENT_ROUTINE_OID:
+				exec_assign_value(estate, var,
+								  ObjectIdGetDatum(estate->func->fn_oid),
+								  false, OIDOID, -1);
+				  break;
+
+			case PLPGSQL_GETDIAG_CURRENT_ROUTINE_SIGNATURE:
+				exec_assign_c_string(estate, var,
+									 estate->func->fn_signature);
+				break;
+
 			default:
 				elog(ERROR, "unrecognized diagnostic item kind: %d",
 					 diag_item->kind);
diff --git a/src/pl/plpgsql/src/pl_funcs.c b/src/pl/plpgsql/src/pl_funcs.c
index 5a6eadccd5..bdf02f36cc 100644
--- a/src/pl/plpgsql/src/pl_funcs.c
+++ b/src/pl/plpgsql/src/pl_funcs.c
@@ -325,6 +325,12 @@ plpgsql_getdiag_kindname(PLpgSQL_getdiag_kind kind)
 			return "TABLE_NAME";
 		case PLPGSQL_GETDIAG_SCHEMA_NAME:
 			return "SCHEMA_NAME";
+		case PLPGSQL_GETDIAG_CURRENT_ROUTINE_NAME:
+			return "PG_CURRENT_ROUTINE_NAME";
+		case PLPGSQL_GETDIAG_CURRENT_ROUTINE_OID:
+			return "PG_CURRENT_ROUTINE_OID";
+		case PLPGSQL_GETDIAG_CURRENT_ROUTINE_SIGNATURE:
+			return "PG_CURRENT_ROUTINE_SIGNATURE";
 	}
 
 	return "unknown";
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index edeb72c380..9ea21fec3d 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -318,6 +318,9 @@ static	void			check_raise_parameters(PLpgSQL_stmt_raise *stmt);
 %token <keyword>	K_OR
 %token <keyword>	K_PERFORM
 %token <keyword>	K_PG_CONTEXT
+%token <keyword>	K_PG_CURRENT_ROUTINE_NAME
+%token <keyword>	K_PG_CURRENT_ROUTINE_OID
+%token <keyword>	K_PG_CURRENT_ROUTINE_SIGNATURE
 %token <keyword>	K_PG_DATATYPE_NAME
 %token <keyword>	K_PG_EXCEPTION_CONTEXT
 %token <keyword>	K_PG_EXCEPTION_DETAIL
@@ -1035,6 +1038,9 @@ stmt_getdiag	: K_GET getdiag_area_opt K_DIAGNOSTICS getdiag_list ';'
 									break;
 								/* these fields are allowed in either case */
 								case PLPGSQL_GETDIAG_CONTEXT:
+								case PLPGSQL_GETDIAG_CURRENT_ROUTINE_NAME:
+								case PLPGSQL_GETDIAG_CURRENT_ROUTINE_OID:
+								case PLPGSQL_GETDIAG_CURRENT_ROUTINE_SIGNATURE:
 									break;
 								default:
 									elog(ERROR, "unrecognized diagnostic item kind: %d",
@@ -1123,6 +1129,15 @@ getdiag_item :
 						else if (tok_is_keyword(tok, &yylval,
 												K_RETURNED_SQLSTATE, "returned_sqlstate"))
 							$$ = PLPGSQL_GETDIAG_RETURNED_SQLSTATE;
+						else if (tok_is_keyword(tok, &yylval,
+												K_PG_CURRENT_ROUTINE_NAME, "pg_current_routine_name"))
+							$$ = PLPGSQL_GETDIAG_CURRENT_ROUTINE_NAME;
+						else if (tok_is_keyword(tok, &yylval,
+												K_PG_CURRENT_ROUTINE_OID, "pg_current_routine_oid"))
+							$$ = PLPGSQL_GETDIAG_CURRENT_ROUTINE_OID;
+						else if (tok_is_keyword(tok, &yylval,
+												K_PG_CURRENT_ROUTINE_SIGNATURE, "pg_current_routine_signature"))
+							$$ = PLPGSQL_GETDIAG_CURRENT_ROUTINE_SIGNATURE;
 						else
 							yyerror("unrecognized GET DIAGNOSTICS item");
 					}
@@ -2523,6 +2538,9 @@ unreserved_keyword	:
 				| K_OPEN
 				| K_OPTION
 				| K_PERFORM
+				| K_PG_CURRENT_ROUTINE_NAME
+				| K_PG_CURRENT_ROUTINE_OID
+				| K_PG_CURRENT_ROUTINE_SIGNATURE
 				| K_PG_CONTEXT
 				| K_PG_DATATYPE_NAME
 				| K_PG_EXCEPTION_CONTEXT
diff --git a/src/pl/plpgsql/src/pl_unreserved_kwlist.h b/src/pl/plpgsql/src/pl_unreserved_kwlist.h
index 466bdc7a20..6d5a14eb28 100644
--- a/src/pl/plpgsql/src/pl_unreserved_kwlist.h
+++ b/src/pl/plpgsql/src/pl_unreserved_kwlist.h
@@ -81,6 +81,9 @@ PG_KEYWORD("open", K_OPEN)
 PG_KEYWORD("option", K_OPTION)
 PG_KEYWORD("perform", K_PERFORM)
 PG_KEYWORD("pg_context", K_PG_CONTEXT)
+PG_KEYWORD("pg_current_routine_name", K_PG_CURRENT_ROUTINE_NAME)
+PG_KEYWORD("pg_current_routine_oid", K_PG_CURRENT_ROUTINE_OID)
+PG_KEYWORD("pg_current_routine_signature", K_PG_CURRENT_ROUTINE_SIGNATURE)
 PG_KEYWORD("pg_datatype_name", K_PG_DATATYPE_NAME)
 PG_KEYWORD("pg_exception_context", K_PG_EXCEPTION_CONTEXT)
 PG_KEYWORD("pg_exception_detail", K_PG_EXCEPTION_DETAIL)
diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h
index 355c9f678d..42c094fb48 100644
--- a/src/pl/plpgsql/src/plpgsql.h
+++ b/src/pl/plpgsql/src/plpgsql.h
@@ -157,7 +157,10 @@ typedef enum PLpgSQL_getdiag_kind
 	PLPGSQL_GETDIAG_DATATYPE_NAME,
 	PLPGSQL_GETDIAG_MESSAGE_TEXT,
 	PLPGSQL_GETDIAG_TABLE_NAME,
-	PLPGSQL_GETDIAG_SCHEMA_NAME
+	PLPGSQL_GETDIAG_SCHEMA_NAME,
+	PLPGSQL_GETDIAG_CURRENT_ROUTINE_NAME,
+	PLPGSQL_GETDIAG_CURRENT_ROUTINE_OID,
+	PLPGSQL_GETDIAG_CURRENT_ROUTINE_SIGNATURE
 } PLpgSQL_getdiag_kind;
 
 /*

Pavel Stehule

pavel.stehule@gmail.com

almost 3 years ago

In reply to: Kirk Wolak (#7)

Re: possible proposal plpgsql GET DIAGNOSTICS oid = PG_ROUTINE_OID

po 27. 3. 2023 v 5:36 odesílatel Kirk Wolak <wolakk@gmail.com> napsal:

On Sun, Mar 26, 2023 at 5:37 PM Kirk Wolak <wolakk@gmail.com> wrote:

On Wed, Feb 8, 2023 at 10:56 AM Kirk 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 7:33 odesílatel Julien Rouhaud <rjuju123@gmail.com>
napsal:

On Tue, Feb 07, 2023 at 08:48:22PM +0100, Pavel Stehule wrote:

GET DIAGNOSTICS _oid = 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 = 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

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

Okay, I have modified the documentation and made sure it compiles. They
were simple enough changes.
I am attaching this updated patch.

I have marked the item Ready for Commiter...

Thank you for doc and for review

Regards

Pavel

Show quoted text

Thanks for your patience. I now have a workable hacking environment!

Regards - Kirk

Tom Lane

tgl@sss.pgh.pa.us

almost 3 years ago

In reply to: Pavel Stehule (#8)

Re: possible proposal plpgsql GET DIAGNOSTICS oid = PG_ROUTINE_OID

Pavel Stehule <pavel.stehule@gmail.com> writes:

po 27. 3. 2023 v 5:36 odesílatel Kirk Wolak <wolakk@gmail.com> napsal:

I have marked the item Ready for Commiter...

Thank you for doc and for review

I'm kind of surprised there was any interest in this proposal at all,
TBH, but apparently there is some. Still, I think you over-engineered
it by doing more than the original proposal of making the function OID
available. The other things can be had by casting the OID to regproc
or regprocedure, so I'd be inclined to add just one new keyword not
three. Besides, your implementation is a bit inconsistent: relying
on fn_signature could return a result that is stale or doesn't conform
to the current search_path.

regards, tom lane

#10

Pavel Stehule

pavel.stehule@gmail.com

almost 3 years ago

In reply to: Tom Lane (#9)

1 attachment(s)

Re: possible proposal plpgsql GET DIAGNOSTICS oid = PG_ROUTINE_OID

po 3. 4. 2023 v 19:37 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

Pavel Stehule <pavel.stehule@gmail.com> writes:

po 27. 3. 2023 v 5:36 odesílatel Kirk Wolak <wolakk@gmail.com> napsal:

I have marked the item Ready for Commiter...

Thank you for doc and for review

I'm kind of surprised there was any interest in this proposal at all,
TBH, but apparently there is some. Still, I think you over-engineered
it by doing more than the original proposal of making the function OID
available. The other things can be had by casting the OID to regproc
or regprocedure, so I'd be inclined to add just one new keyword not
three. Besides, your implementation is a bit inconsistent: relying
on fn_signature could return a result that is stale or doesn't conform
to the current search_path.

There is reduced patch + regress tests

Regards

Pavel

Show quoted text

regards, tom lane

Attachments:

plpgsql-get-routine-oid.patchtext/x-patch; charset=US-ASCII; name=plpgsql-get-routine-oid.patchDownload

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 7c8a49fe43..4df1b71904 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -1639,6 +1639,11 @@ GET DIAGNOSTICS integer_var = ROW_COUNT;
          <entry>line(s) of text describing the current call stack
           (see <xref linkend="plpgsql-call-stack"/>)</entry>
         </row>
+        <row>
+         <entry><literal>PG_CURRENT_ROUTINE_OID</literal></entry>
+         <entry><type>oid</type></entry>
+         <entry>oid of the function currently running</entry>
+        </row>
        </tbody>
       </tgroup>
      </table>
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index b0a2cac227..ed9d26258b 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -2475,6 +2475,12 @@ exec_stmt_getdiag(PLpgSQL_execstate *estate, PLpgSQL_stmt_getdiag *stmt)
 				}
 				break;
 
+			case PLPGSQL_GETDIAG_CURRENT_ROUTINE_OID:
+				exec_assign_value(estate, var,
+								  ObjectIdGetDatum(estate->func->fn_oid),
+								  false, OIDOID, -1);
+				  break;
+
 			default:
 				elog(ERROR, "unrecognized diagnostic item kind: %d",
 					 diag_item->kind);
diff --git a/src/pl/plpgsql/src/pl_funcs.c b/src/pl/plpgsql/src/pl_funcs.c
index 5a6eadccd5..4ebf393646 100644
--- a/src/pl/plpgsql/src/pl_funcs.c
+++ b/src/pl/plpgsql/src/pl_funcs.c
@@ -325,6 +325,8 @@ plpgsql_getdiag_kindname(PLpgSQL_getdiag_kind kind)
 			return "TABLE_NAME";
 		case PLPGSQL_GETDIAG_SCHEMA_NAME:
 			return "SCHEMA_NAME";
+		case PLPGSQL_GETDIAG_CURRENT_ROUTINE_OID:
+			return "PG_CURRENT_ROUTINE_OID";
 	}
 
 	return "unknown";
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index edeb72c380..6e81cf774d 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -318,6 +318,7 @@ static	void			check_raise_parameters(PLpgSQL_stmt_raise *stmt);
 %token <keyword>	K_OR
 %token <keyword>	K_PERFORM
 %token <keyword>	K_PG_CONTEXT
+%token <keyword>	K_PG_CURRENT_ROUTINE_OID
 %token <keyword>	K_PG_DATATYPE_NAME
 %token <keyword>	K_PG_EXCEPTION_CONTEXT
 %token <keyword>	K_PG_EXCEPTION_DETAIL
@@ -1035,6 +1036,7 @@ stmt_getdiag	: K_GET getdiag_area_opt K_DIAGNOSTICS getdiag_list ';'
 									break;
 								/* these fields are allowed in either case */
 								case PLPGSQL_GETDIAG_CONTEXT:
+								case PLPGSQL_GETDIAG_CURRENT_ROUTINE_OID:
 									break;
 								default:
 									elog(ERROR, "unrecognized diagnostic item kind: %d",
@@ -1123,6 +1125,9 @@ getdiag_item :
 						else if (tok_is_keyword(tok, &yylval,
 												K_RETURNED_SQLSTATE, "returned_sqlstate"))
 							$$ = PLPGSQL_GETDIAG_RETURNED_SQLSTATE;
+						else if (tok_is_keyword(tok, &yylval,
+												K_PG_CURRENT_ROUTINE_OID, "pg_current_routine_oid"))
+							$$ = PLPGSQL_GETDIAG_CURRENT_ROUTINE_OID;
 						else
 							yyerror("unrecognized GET DIAGNOSTICS item");
 					}
@@ -2523,6 +2528,7 @@ unreserved_keyword	:
 				| K_OPEN
 				| K_OPTION
 				| K_PERFORM
+				| K_PG_CURRENT_ROUTINE_OID
 				| K_PG_CONTEXT
 				| K_PG_DATATYPE_NAME
 				| K_PG_EXCEPTION_CONTEXT
diff --git a/src/pl/plpgsql/src/pl_unreserved_kwlist.h b/src/pl/plpgsql/src/pl_unreserved_kwlist.h
index 466bdc7a20..b569d75708 100644
--- a/src/pl/plpgsql/src/pl_unreserved_kwlist.h
+++ b/src/pl/plpgsql/src/pl_unreserved_kwlist.h
@@ -81,6 +81,7 @@ PG_KEYWORD("open", K_OPEN)
 PG_KEYWORD("option", K_OPTION)
 PG_KEYWORD("perform", K_PERFORM)
 PG_KEYWORD("pg_context", K_PG_CONTEXT)
+PG_KEYWORD("pg_current_routine_oid", K_PG_CURRENT_ROUTINE_OID)
 PG_KEYWORD("pg_datatype_name", K_PG_DATATYPE_NAME)
 PG_KEYWORD("pg_exception_context", K_PG_EXCEPTION_CONTEXT)
 PG_KEYWORD("pg_exception_detail", K_PG_EXCEPTION_DETAIL)
diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h
index 355c9f678d..258ce4dec7 100644
--- a/src/pl/plpgsql/src/plpgsql.h
+++ b/src/pl/plpgsql/src/plpgsql.h
@@ -157,7 +157,8 @@ typedef enum PLpgSQL_getdiag_kind
 	PLPGSQL_GETDIAG_DATATYPE_NAME,
 	PLPGSQL_GETDIAG_MESSAGE_TEXT,
 	PLPGSQL_GETDIAG_TABLE_NAME,
-	PLPGSQL_GETDIAG_SCHEMA_NAME
+	PLPGSQL_GETDIAG_SCHEMA_NAME,
+	PLPGSQL_GETDIAG_CURRENT_ROUTINE_OID
 } PLpgSQL_getdiag_kind;
 
 /*
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index 2d26be1a81..56ed4e14bf 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -5798,3 +5798,29 @@ END; $$ LANGUAGE plpgsql;
 ERROR:  "x" is not a scalar variable
 LINE 3:   GET DIAGNOSTICS x = ROW_COUNT;
                           ^
+--
+-- Check pg_current_routine_oid
+--
+-- Note: the result cannot be displayed, but we can check so
+-- the related value is assigned without errors.
+--
+DO $$
+DECLARE
+  fn_oid oid;
+BEGIN
+  GET DIAGNOSTICS fn_oid = PG_CURRENT_ROUTINE_OID;
+  RAISE NOTICE 'ok';
+END;
+$$;
+NOTICE:  ok
+DO $$
+DECLARE
+  fn_oid oid;
+BEGIN
+  RAISE EXCEPTION 'error';
+EXCEPTION WHEN others THEN
+  GET STACKED DIAGNOSTICS fn_oid = PG_CURRENT_ROUTINE_OID;
+  RAISE NOTICE 'ok';
+END;
+$$;
+NOTICE:  ok
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index 98365e087f..8fb56b93ef 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -4731,3 +4731,29 @@ BEGIN
   GET DIAGNOSTICS x = ROW_COUNT;
   RETURN;
 END; $$ LANGUAGE plpgsql;
+
+--
+-- Check pg_current_routine_oid
+--
+-- Note: the result cannot be displayed, but we can check so
+-- the related value is assigned without errors.
+--
+DO $$
+DECLARE
+  fn_oid oid;
+BEGIN
+  GET DIAGNOSTICS fn_oid = PG_CURRENT_ROUTINE_OID;
+  RAISE NOTICE 'ok';
+END;
+$$;
+
+DO $$
+DECLARE
+  fn_oid oid;
+BEGIN
+  RAISE EXCEPTION 'error';
+EXCEPTION WHEN others THEN
+  GET STACKED DIAGNOSTICS fn_oid = PG_CURRENT_ROUTINE_OID;
+  RAISE NOTICE 'ok';
+END;
+$$;

#11

Tom Lane

tgl@sss.pgh.pa.us

almost 3 years ago

In reply to: Pavel Stehule (#10)

Re: possible proposal plpgsql GET DIAGNOSTICS oid = PG_ROUTINE_OID

Pavel Stehule <pavel.stehule@gmail.com> writes:

There is reduced patch + regress tests

One more thing: I do not think it's appropriate to allow this in
GET STACKED DIAGNOSTICS. That's about reporting the place where
an error occurred, not the current location. Eventually it might
be interesting to retrieve the OID of the function that contained
the error, but that would be a pretty complicated patch and I am
not sure it's worth it. In the meantime I think we should just
forbid it.

If we do that, then the confusion you were concerned about upthread
goes away and we could shorten the keyword back down to "pg_routine_oid",
which seems like a good thing for our carpal tunnels.

Thoughts?

regards, tom lane

#12

Pavel Stehule

pavel.stehule@gmail.com

almost 3 years ago

In reply to: Tom Lane (#11)

1 attachment(s)

Re: possible proposal plpgsql GET DIAGNOSTICS oid = PG_ROUTINE_OID

út 4. 4. 2023 v 16:20 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

Pavel Stehule <pavel.stehule@gmail.com> writes:

There is reduced patch + regress tests

One more thing: I do not think it's appropriate to allow this in
GET STACKED DIAGNOSTICS. That's about reporting the place where
an error occurred, not the current location. Eventually it might
be interesting to retrieve the OID of the function that contained
the error, but that would be a pretty complicated patch and I am
not sure it's worth it. In the meantime I think we should just
forbid it.

If we do that, then the confusion you were concerned about upthread
goes away and we could shorten the keyword back down to "pg_routine_oid",
which seems like a good thing for our carpal tunnels.

Thoughts?

has sense

updated patch attached

Regards

Pavel

Show quoted text

regards, tom lane

Attachments:

plpgsql-get-routine-oid.patchtext/x-patch; charset=US-ASCII; name=plpgsql-get-routine-oid.patchDownload

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 7c8a49fe43..163ca48f31 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -1639,6 +1639,11 @@ GET DIAGNOSTICS integer_var = ROW_COUNT;
          <entry>line(s) of text describing the current call stack
           (see <xref linkend="plpgsql-call-stack"/>)</entry>
         </row>
+        <row>
+         <entry><literal>PG_ROUTINE_OID</literal></entry>
+         <entry><type>oid</type></entry>
+         <entry>oid of the function currently running</entry>
+        </row>
        </tbody>
       </tgroup>
      </table>
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index b0a2cac227..1603ba073d 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -2475,6 +2475,12 @@ exec_stmt_getdiag(PLpgSQL_execstate *estate, PLpgSQL_stmt_getdiag *stmt)
 				}
 				break;
 
+			case PLPGSQL_GETDIAG_ROUTINE_OID:
+				exec_assign_value(estate, var,
+								  ObjectIdGetDatum(estate->func->fn_oid),
+								  false, OIDOID, -1);
+				  break;
+
 			default:
 				elog(ERROR, "unrecognized diagnostic item kind: %d",
 					 diag_item->kind);
diff --git a/src/pl/plpgsql/src/pl_funcs.c b/src/pl/plpgsql/src/pl_funcs.c
index 5a6eadccd5..1b0c857b7d 100644
--- a/src/pl/plpgsql/src/pl_funcs.c
+++ b/src/pl/plpgsql/src/pl_funcs.c
@@ -325,6 +325,8 @@ plpgsql_getdiag_kindname(PLpgSQL_getdiag_kind kind)
 			return "TABLE_NAME";
 		case PLPGSQL_GETDIAG_SCHEMA_NAME:
 			return "SCHEMA_NAME";
+		case PLPGSQL_GETDIAG_ROUTINE_OID:
+			return "PG_ROUTINE_OID";
 	}
 
 	return "unknown";
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index edeb72c380..bf2c04852b 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -318,6 +318,7 @@ static	void			check_raise_parameters(PLpgSQL_stmt_raise *stmt);
 %token <keyword>	K_OR
 %token <keyword>	K_PERFORM
 %token <keyword>	K_PG_CONTEXT
+%token <keyword>	K_PG_ROUTINE_OID
 %token <keyword>	K_PG_DATATYPE_NAME
 %token <keyword>	K_PG_EXCEPTION_CONTEXT
 %token <keyword>	K_PG_EXCEPTION_DETAIL
@@ -1008,6 +1009,7 @@ stmt_getdiag	: K_GET getdiag_area_opt K_DIAGNOSTICS getdiag_list ';'
 							{
 								/* these fields are disallowed in stacked case */
 								case PLPGSQL_GETDIAG_ROW_COUNT:
+								case PLPGSQL_GETDIAG_ROUTINE_OID:
 									if (new->is_stacked)
 										ereport(ERROR,
 												(errcode(ERRCODE_SYNTAX_ERROR),
@@ -1123,6 +1125,9 @@ getdiag_item :
 						else if (tok_is_keyword(tok, &yylval,
 												K_RETURNED_SQLSTATE, "returned_sqlstate"))
 							$$ = PLPGSQL_GETDIAG_RETURNED_SQLSTATE;
+						else if (tok_is_keyword(tok, &yylval,
+												K_PG_ROUTINE_OID, "pg_routine_oid"))
+							$$ = PLPGSQL_GETDIAG_ROUTINE_OID;
 						else
 							yyerror("unrecognized GET DIAGNOSTICS item");
 					}
@@ -2523,6 +2528,7 @@ unreserved_keyword	:
 				| K_OPEN
 				| K_OPTION
 				| K_PERFORM
+				| K_PG_ROUTINE_OID
 				| K_PG_CONTEXT
 				| K_PG_DATATYPE_NAME
 				| K_PG_EXCEPTION_CONTEXT
diff --git a/src/pl/plpgsql/src/pl_unreserved_kwlist.h b/src/pl/plpgsql/src/pl_unreserved_kwlist.h
index 466bdc7a20..3e258a6bb9 100644
--- a/src/pl/plpgsql/src/pl_unreserved_kwlist.h
+++ b/src/pl/plpgsql/src/pl_unreserved_kwlist.h
@@ -85,6 +85,7 @@ PG_KEYWORD("pg_datatype_name", K_PG_DATATYPE_NAME)
 PG_KEYWORD("pg_exception_context", K_PG_EXCEPTION_CONTEXT)
 PG_KEYWORD("pg_exception_detail", K_PG_EXCEPTION_DETAIL)
 PG_KEYWORD("pg_exception_hint", K_PG_EXCEPTION_HINT)
+PG_KEYWORD("pg_routine_oid", K_PG_ROUTINE_OID)
 PG_KEYWORD("print_strict_params", K_PRINT_STRICT_PARAMS)
 PG_KEYWORD("prior", K_PRIOR)
 PG_KEYWORD("query", K_QUERY)
diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h
index 355c9f678d..7e574b8052 100644
--- a/src/pl/plpgsql/src/plpgsql.h
+++ b/src/pl/plpgsql/src/plpgsql.h
@@ -157,7 +157,8 @@ typedef enum PLpgSQL_getdiag_kind
 	PLPGSQL_GETDIAG_DATATYPE_NAME,
 	PLPGSQL_GETDIAG_MESSAGE_TEXT,
 	PLPGSQL_GETDIAG_TABLE_NAME,
-	PLPGSQL_GETDIAG_SCHEMA_NAME
+	PLPGSQL_GETDIAG_SCHEMA_NAME,
+	PLPGSQL_GETDIAG_ROUTINE_OID
 } PLpgSQL_getdiag_kind;
 
 /*
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index 2d26be1a81..df01788a3a 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -5798,3 +5798,32 @@ END; $$ LANGUAGE plpgsql;
 ERROR:  "x" is not a scalar variable
 LINE 3:   GET DIAGNOSTICS x = ROW_COUNT;
                           ^
+--
+-- Check pg_current_routine_oid
+--
+-- Note: the result cannot be displayed, but we can check so
+-- the related value is assigned without errors.
+--
+DO $$
+DECLARE
+  fn_oid oid;
+BEGIN
+  GET DIAGNOSTICS fn_oid = PG_ROUTINE_OID;
+  RAISE NOTICE 'ok';
+END;
+$$;
+NOTICE:  ok
+-- should fail, PG_ROUTINE_OID is not allowed in GET STACKED DIAGNOSTICS
+DO $$
+DECLARE
+  fn_oid oid;
+BEGIN
+  RAISE EXCEPTION 'error';
+EXCEPTION WHEN others THEN
+  GET STACKED DIAGNOSTICS fn_oid = PG_ROUTINE_OID;
+  RAISE NOTICE 'ok';
+END;
+$$;
+ERROR:  diagnostics item PG_ROUTINE_OID is not allowed in GET STACKED DIAGNOSTICS
+LINE 7:   GET STACKED DIAGNOSTICS fn_oid = PG_ROUTINE_OID;
+          ^
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index 98365e087f..51640d6b37 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -4731,3 +4731,30 @@ BEGIN
   GET DIAGNOSTICS x = ROW_COUNT;
   RETURN;
 END; $$ LANGUAGE plpgsql;
+
+--
+-- Check pg_current_routine_oid
+--
+-- Note: the result cannot be displayed, but we can check so
+-- the related value is assigned without errors.
+--
+DO $$
+DECLARE
+  fn_oid oid;
+BEGIN
+  GET DIAGNOSTICS fn_oid = PG_ROUTINE_OID;
+  RAISE NOTICE 'ok';
+END;
+$$;
+
+-- should fail, PG_ROUTINE_OID is not allowed in GET STACKED DIAGNOSTICS
+DO $$
+DECLARE
+  fn_oid oid;
+BEGIN
+  RAISE EXCEPTION 'error';
+EXCEPTION WHEN others THEN
+  GET STACKED DIAGNOSTICS fn_oid = PG_ROUTINE_OID;
+  RAISE NOTICE 'ok';
+END;
+$$;

#13

Tom Lane

tgl@sss.pgh.pa.us

almost 3 years ago

In reply to: Pavel Stehule (#12)

Re: possible proposal plpgsql GET DIAGNOSTICS oid = PG_ROUTINE_OID

Pavel Stehule <pavel.stehule@gmail.com> writes:

út 4. 4. 2023 v 16:20 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

If we do that, then the confusion you were concerned about upthread
goes away and we could shorten the keyword back down to "pg_routine_oid",
which seems like a good thing for our carpal tunnels.

has sense

OK, pushed like that with some cosmetic adjustments (better test
case, mostly).

regards, tom lane

#14

Pavel Stehule

pavel.stehule@gmail.com

almost 3 years ago

In reply to: Tom Lane (#13)

Re: possible proposal plpgsql GET DIAGNOSTICS oid = PG_ROUTINE_OID

út 4. 4. 2023 v 19:34 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

Pavel Stehule <pavel.stehule@gmail.com> writes:

út 4. 4. 2023 v 16:20 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

If we do that, then the confusion you were concerned about upthread
goes away and we could shorten the keyword back down to

"pg_routine_oid",

which seems like a good thing for our carpal tunnels.

has sense

OK, pushed like that with some cosmetic adjustments (better test
case, mostly).

Thank you very much

Regards

Pavel

Show quoted text

regards, tom lane