Adding a pg_get_owned_sequence function?

Hi hackers,

I've always been annoyed by the fact that pg_get_serial_sequence takes
the table and returns the sequence as strings rather than regclass. And
since identity columns were added, the name is misleading as well (which
is even acknowledged in the docs, together with a suggestion for a
better name).

So, instead of making excuses in the documentation, I thought why not
add a new function which addresses all of these issues, and document the
old one as a backward-compatibilty wrapper?

Please see the attached patch for my stab at this.

- ilmari

From 7fd37c15920b7d2e87edef4351932559e2c9ef4f Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= <ilmari@ilmari.org>
Date: Fri, 9 Jun 2023 19:55:58 +0100
Subject: [PATCH] Add pg_get_owned_sequence function

This is the name the docs say `pg_get_serial_sequence` sholud have
had, and gives us the opportunity to change the return and table
argument types to `regclass` and the column argument to `name`,
instead of using `text` everywhere.  This matches what's in catalogs,
and requires less explaining than the rules for
`pg_get_serial_sequence`.
---
 doc/src/sgml/func.sgml                 | 46 ++++++++++++-----
 src/backend/utils/adt/ruleutils.c      | 69 +++++++++++++++++++-------
 src/include/catalog/pg_proc.dat        |  3 ++
 src/test/regress/expected/identity.out |  6 +++
 src/test/regress/expected/sequence.out |  6 +++
 src/test/regress/sql/identity.sql      |  1 +
 src/test/regress/sql/sequence.sql      |  1 +
 7 files changed, 102 insertions(+), 30 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5a47ce4343..687a8480e6 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -24327,6 +24327,35 @@
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_owned_sequence</primary>
+        </indexterm>
+        <function>pg_get_owned_sequence</function> ( <parameter>table</parameter> <type>regclass</type>,
<parameter>column</parameter><type>name</type> )
 
+        <returnvalue>regclass</returnvalue>
+       </para>
+       <para>
+        Returns the sequence associated with a column, or NULL if no sequence
+        is associated with the column.  If the column is an identity column,
+        the associated sequence is the sequence internally created for that
+        column.  For columns created using one of the serial types
+        (<type>serial</type>, <type>smallserial</type>, <type>bigserial</type>),
+        it is the sequence created for that serial column definition.  In the
+        latter case, the association can be modified or removed
+        with <command>ALTER SEQUENCE OWNED BY</command>.  The result is
+        suitable for passing to the sequence functions (see
+        <xref linkend="functions-sequence"/>).
+       </para>
+       <para>
+        A typical use is in reading the current value of the sequence for an
+        identity or serial column, for example:
+<programlisting>
+SELECT currval(pg_get_owned_sequence('sometable', 'id'));
+</programlisting>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
@@ -24336,19 +24365,10 @@
         <returnvalue>text</returnvalue>
        </para>
        <para>
-        Returns the name of the sequence associated with a column,
-        or NULL if no sequence is associated with the column.
-        If the column is an identity column, the associated sequence is the
-        sequence internally created for that column.
-        For columns created using one of the serial types
-        (<type>serial</type>, <type>smallserial</type>, <type>bigserial</type>),
-        it is the sequence created for that serial column definition.
-        In the latter case, the association can be modified or removed
-        with <command>ALTER SEQUENCE OWNED BY</command>.
-        (This function probably should have been
-        called <function>pg_get_owned_sequence</function>; its current name
-        reflects the fact that it has historically been used with serial-type
-        columns.)  The first parameter is a table name with optional
+        A backwards-compatibility wrapper
+        for <function>pg_get_owned_sequence</function>, which
+        uses <type>text</type> for the table and sequence names instead of
+        <type>regclass</type>.  The first parameter is a table name with optional
         schema, and the second parameter is a column name.  Because the first
         parameter potentially contains both schema and table names, it is
         parsed per usual SQL rules, meaning it is lower-cased by default.
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index d3a973d86b..b20a1e7583 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -518,6 +518,7 @@ static char *generate_qualified_type_name(Oid typid);
 static text *string_to_text(char *str);
 static char *flatten_reloptions(Oid relid);
 static void get_reloptions(StringInfo buf, Datum reloptions);
+static Oid pg_get_owned_sequence_internal(Oid tableOid, char *column);
 
 #define only_marker(rte)  ((rte)->inh ? "" : "ONLY ")
 
@@ -2763,6 +2764,28 @@ pg_get_userbyid(PG_FUNCTION_ARGS)
 }
 
 
+/*
+ * pg_get_owned_sequence
+ *        Get the sequence used by an identity or serial column.
+ */
+Datum
+pg_get_owned_sequence(PG_FUNCTION_ARGS)
+{
+    Oid            tableOid = PG_GETARG_OID(0);
+    char       *column = NameStr(*PG_GETARG_NAME(1));
+    Oid            sequenceId;
+
+    sequenceId = pg_get_owned_sequence_internal(tableOid, column);
+
+    if (OidIsValid(sequenceId))
+    {
+        PG_RETURN_OID(sequenceId);
+    }
+
+    PG_RETURN_NULL();
+}
+
+
 /*
  * pg_get_serial_sequence
  *        Get the name of the sequence used by an identity or serial column,
@@ -2778,6 +2801,32 @@ pg_get_serial_sequence(PG_FUNCTION_ARGS)
     RangeVar   *tablerv;
     Oid            tableOid;
     char       *column;
+    Oid            sequenceId;
+
+    /* Look up table name.  Can't lock it - we might not have privileges. */
+    tablerv = makeRangeVarFromNameList(textToQualifiedNameList(tablename));
+    tableOid = RangeVarGetRelid(tablerv, NoLock, false);
+
+    column = text_to_cstring(columnname);
+
+    sequenceId = pg_get_owned_sequence_internal(tableOid, column);
+
+    if (OidIsValid(sequenceId))
+    {
+        char       *result;
+
+        result = generate_qualified_relation_name(sequenceId);
+
+        PG_RETURN_TEXT_P(string_to_text(result));
+    }
+
+    PG_RETURN_NULL();
+}
+
+
+static Oid
+pg_get_owned_sequence_internal(Oid tableOid, char *column)
+{
     AttrNumber    attnum;
     Oid            sequenceId = InvalidOid;
     Relation    depRel;
@@ -2785,19 +2834,13 @@ pg_get_serial_sequence(PG_FUNCTION_ARGS)
     SysScanDesc scan;
     HeapTuple    tup;
 
-    /* Look up table name.  Can't lock it - we might not have privileges. */
-    tablerv = makeRangeVarFromNameList(textToQualifiedNameList(tablename));
-    tableOid = RangeVarGetRelid(tablerv, NoLock, false);
-
     /* Get the number of the column */
-    column = text_to_cstring(columnname);
-
     attnum = get_attnum(tableOid, column);
     if (attnum == InvalidAttrNumber)
         ereport(ERROR,
                 (errcode(ERRCODE_UNDEFINED_COLUMN),
                  errmsg("column \"%s\" of relation \"%s\" does not exist",
-                        column, tablerv->relname)));
+                        column, get_relation_name(tableOid))));
 
     /* Search the dependency table for the dependent sequence */
     depRel = table_open(DependRelationId, AccessShareLock);
@@ -2841,19 +2884,11 @@ pg_get_serial_sequence(PG_FUNCTION_ARGS)
     systable_endscan(scan);
     table_close(depRel, AccessShareLock);
 
-    if (OidIsValid(sequenceId))
-    {
-        char       *result;
-
-        result = generate_qualified_relation_name(sequenceId);
-
-        PG_RETURN_TEXT_P(string_to_text(result));
-    }
-
-    PG_RETURN_NULL();
+    return sequenceId;
 }
 
 
+
 /*
  * pg_get_functiondef
  *        Returns the complete "CREATE OR REPLACE FUNCTION ..." statement for
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6996073989..34270a4c44 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3809,6 +3809,9 @@
 { oid => '1716', descr => 'deparse an encoded expression',
   proname => 'pg_get_expr', provolatile => 's', prorettype => 'text',
   proargtypes => 'pg_node_tree oid', prosrc => 'pg_get_expr' },
+{ oid => '8973', descr => 'name of sequence for an identity or serial column',
+  proname => 'pg_get_owned_sequence', provolatile => 's', prorettype => 'regclass',
+  proargtypes => 'regclass name', prosrc => 'pg_get_owned_sequence' },
 { oid => '1665', descr => 'name of sequence for a serial column',
   proname => 'pg_get_serial_sequence', provolatile => 's', prorettype => 'text',
   proargtypes => 'text text', prosrc => 'pg_get_serial_sequence' },
diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out
index 5f03d8e14f..dc8aa102ac 100644
--- a/src/test/regress/expected/identity.out
+++ b/src/test/regress/expected/identity.out
@@ -32,6 +32,12 @@ SELECT pg_get_serial_sequence('itest1', 'a');
  public.itest1_a_seq
 (1 row)
 
+SELECT pg_get_owned_sequence('itest1', 'a');
+ pg_get_owned_sequence 
+-----------------------
+ itest1_a_seq
+(1 row)
+
 \d itest1_a_seq
                     Sequence "public.itest1_a_seq"
   Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache 
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index 7cb2f7cc02..283fff6a31 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -84,6 +84,12 @@ SELECT pg_get_serial_sequence('serialTest1', 'f2');
  public.serialtest1_f2_seq
 (1 row)
 
+SELECT pg_get_owned_sequence('serialTest1', 'f2');
+ pg_get_owned_sequence 
+-----------------------
+ serialtest1_f2_seq
+(1 row)
+
 -- test smallserial / bigserial
 CREATE TABLE serialTest2 (f1 text, f2 serial, f3 smallserial, f4 serial2,
   f5 bigserial, f6 serial8);
diff --git a/src/test/regress/sql/identity.sql b/src/test/regress/sql/identity.sql
index 9b8db2e4a3..3d78643b76 100644
--- a/src/test/regress/sql/identity.sql
+++ b/src/test/regress/sql/identity.sql
@@ -13,6 +13,7 @@ SELECT table_name, column_name, column_default, is_nullable, is_identity, identi
 SELECT sequence_name FROM information_schema.sequences WHERE sequence_name LIKE 'itest%';
 
 SELECT pg_get_serial_sequence('itest1', 'a');
+SELECT pg_get_owned_sequence('itest1', 'a');
 
 \d itest1_a_seq
 
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index 674f5f1f66..828d3ede8b 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -61,6 +61,7 @@ INSERT INTO serialTest1 VALUES ('wrong', NULL);
 SELECT * FROM serialTest1;
 
 SELECT pg_get_serial_sequence('serialTest1', 'f2');
+SELECT pg_get_owned_sequence('serialTest1', 'f2');
 
 -- test smallserial / bigserial
 CREATE TABLE serialTest2 (f1 text, f2 serial, f3 smallserial, f4 serial2,
-- 
2.39.2