From 7fd37c15920b7d2e87edef4351932559e2c9ef4f Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= 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 @@ + + + + pg_get_owned_sequence + + pg_get_owned_sequence ( table regclass, column name ) + regclass + + + 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 + (serial, smallserial, bigserial), + it is the sequence created for that serial column definition. In the + latter case, the association can be modified or removed + with ALTER SEQUENCE OWNED BY. The result is + suitable for passing to the sequence functions (see + ). + + + A typical use is in reading the current value of the sequence for an + identity or serial column, for example: + +SELECT currval(pg_get_owned_sequence('sometable', 'id')); + + + + @@ -24336,19 +24365,10 @@ text - 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 - (serial, smallserial, bigserial), - it is the sequence created for that serial column definition. - In the latter case, the association can be modified or removed - with ALTER SEQUENCE OWNED BY. - (This function probably should have been - called pg_get_owned_sequence; 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 pg_get_owned_sequence, which + uses text for the table and sequence names instead of + regclass. 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