Subject: Re: possible proposal plpgsql GET DIAGNOSTICS oid = PG_ROUTINE_OID

From: Kirk Wolak <[email-masked]>
Date: Sun, 26 Mar 2023 17:37:36 -0400

On Wed, Feb 8, 2023 at 10:56 AM Kirk Wolak <[email-masked]> wrote:

> On Wed, Feb 8, 2023 at 3:08 AM Pavel Stehule <[email-masked]>
> wrote:
>
>> hi
>>
>> st 8. 2. 2023 v 7:33 odesílatel Julien Rouhaud <[email-masked]>
>> 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

---

From: Kirk Wolak <[email-masked]>
Date: Sun, 26 Mar 2023 23:36:10 -0400

On Sun, Mar 26, 2023 at 5:37 PM Kirk Wolak <[email-masked]> wrote:

>>> 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
>>>
>>
> 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
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;
 
 /*

---

From: Pavel Stehule <[email-masked]>
Date: Mon, 27 Mar 2023 08:29:29 +0200

Hi

> I have marked the item Ready for Commiter...

Thank you for doc and for review

Regards

Pavel

---

From: Tom Lane <[email-masked]>
Date: Mon, 03 Apr 2023 13:37:31 -0400

> 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

---

From: Pavel Stehule <[email-masked]>
Date: Mon, 3 Apr 2023 20:49:35 +0200

Hi

> to the current search_path.

ok

There is reduced patch + regress tests

Regards

Pavel

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;
 
index 5a6eadccd5..4ebf393646 100644
@@ -325,6 +325,8 @@ plpgsql_getdiag_kindname(PLpgSQL_getdiag_kind kind)
 
 	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;
+$$;

---

From: Tom Lane <[email-masked]>
Date: Tue, 04 Apr 2023 10:20:00 -0400

> 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

---

From: Pavel Stehule <[email-masked]>
Date: Tue, 4 Apr 2023 18:57:12 +0200

> which seems like a good thing for our carpal tunnels.

has sense

updated patch attached

Regards

Pavel

index 7c8a49fe43..163ca48f31 100644
+         <entry><literal>PG_ROUTINE_OID</literal></entry>
index b0a2cac227..1603ba073d 100644
 
+			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;
+$$;

---

From: Tom Lane <[email-masked]>
Date: Tue, 04 Apr 2023 13:34:22 -0400

> has sense

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

			regards, tom lane

---

From: Pavel Stehule <[email-masked]>
Date: Tue, 4 Apr 2023 19:41:53 +0200

> OK, pushed like that with some cosmetic adjustments (better test

Thank you very much

Regards

Pavel