diff options
| -rw-r--r-- | doc/src/sgml/func.sgml | 37 | ||||
| -rw-r--r-- | src/backend/utils/adt/ruleutils.c | 11 | ||||
| -rw-r--r-- | src/test/regress/expected/identity.out | 6 | ||||
| -rw-r--r-- | src/test/regress/expected/sequence.out | 6 | ||||
| -rw-r--r-- | src/test/regress/sql/identity.sql | 2 | ||||
| -rw-r--r-- | src/test/regress/sql/sequence.sql | 2 |
6 files changed, 44 insertions, 20 deletions
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 641b3b8f4e4..2f036015cc2 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17034,8 +17034,7 @@ SELECT pg_type_is_visible('myschema.widget'::regtype); <row> <entry><literal><function>pg_get_serial_sequence(<parameter>table_name</parameter>, <parameter>column_name</parameter>)</function></literal></entry> <entry><type>text</type></entry> - <entry>get name of the sequence that a <type>serial</type>, <type>smallserial</type> or <type>bigserial</type> column - uses</entry> + <entry>get name of the sequence that a serial or identity column uses</entry> </row> <row> <entry><literal><function>pg_get_statisticsobjdef(<parameter>statobj_oid</parameter>)</function></literal></entry> @@ -17223,19 +17222,27 @@ SELECT pg_type_is_visible('myschema.widget'::regtype); <para> <function>pg_get_serial_sequence</function> returns the name of the sequence associated with a column, or NULL if no sequence is associated - with the column. The first input parameter is a table name with - optional schema, and the second parameter is a column name. Because - the first parameter is potentially a schema and table, it is not treated - as a double-quoted identifier, meaning it is lower cased by default, - while the second parameter, being just a column name, is treated as - double-quoted and has its case preserved. The function returns a value - suitably formatted for passing to sequence functions (see <xref - linkend="functions-sequence">). This association can be modified or - removed with <command>ALTER SEQUENCE OWNED BY</>. (The function - probably should have been called - <function>pg_get_owned_sequence</function>; its current name reflects the fact - that it's typically used with <type>serial</> or <type>bigserial</> - columns.) + with the column. If the column is an identity column, the associated + sequence is the sequence internally created for the identity 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, this association can be modified or removed with <command>ALTER + SEQUENCE OWNED BY</>. (The function probably should have been called + <function>pg_get_owned_sequence</function>; its current name reflects the + fact that it has typically been used with <type>serial</> + or <type>bigserial</> columns.) The first input parameter is a table name + with optional schema, and the second parameter is a column name. Because + the first parameter is potentially a schema and table, it is not treated as + a double-quoted identifier, meaning it is lower cased by default, while the + second parameter, being just a column name, is treated as double-quoted and + has its case preserved. The function returns a value suitably formatted + for passing to sequence functions + (see <xref linkend="functions-sequence">). A typical use is in reading the + current value of a sequence for an identity or serial column, for example: +<programlisting> +SELECT currval(pg_get_serial_sequence('sometable', 'id')); +</programlisting> </para> <para> diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 0ea50782181..84759b6149e 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -2322,7 +2322,7 @@ pg_get_userbyid(PG_FUNCTION_ARGS) /* * pg_get_serial_sequence - * Get the name of the sequence used by a serial column, + * Get the name of the sequence used by an identity or serial column, * formatted suitably for passing to setval, nextval or currval. * First parameter is not treated as double-quoted, second parameter * is --- see documentation for reason. @@ -2380,13 +2380,14 @@ pg_get_serial_sequence(PG_FUNCTION_ARGS) Form_pg_depend deprec = (Form_pg_depend) GETSTRUCT(tup); /* - * We assume any auto dependency of a sequence on a column must be - * what we are looking for. (We need the relkind test because indexes - * can also have auto dependencies on columns.) + * Look for an auto dependency (serial column) or internal dependency + * (identity column) of a sequence on a column. (We need the relkind + * test because indexes can also have auto dependencies on columns.) */ if (deprec->classid == RelationRelationId && deprec->objsubid == 0 && - deprec->deptype == DEPENDENCY_AUTO && + (deprec->deptype == DEPENDENCY_AUTO || + deprec->deptype == DEPENDENCY_INTERNAL) && get_rel_relkind(deprec->objid) == RELKIND_SEQUENCE) { sequenceId = deprec->objid; diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out index 88b56dad93b..2800ed7caab 100644 --- a/src/test/regress/expected/identity.out +++ b/src/test/regress/expected/identity.out @@ -26,6 +26,12 @@ SELECT sequence_name FROM information_schema.sequences WHERE sequence_name LIKE --------------- (0 rows) +SELECT pg_get_serial_sequence('itest1', 'a'); + pg_get_serial_sequence +------------------------ + public.itest1_a_seq +(1 row) + CREATE TABLE itest4 (a int, b text); ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error, requires NOT NULL ERROR: column "a" of relation "itest4" must be declared NOT NULL before identity can be added diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out index a43b52cfc1e..ea05a3382b8 100644 --- a/src/test/regress/expected/sequence.out +++ b/src/test/regress/expected/sequence.out @@ -79,6 +79,12 @@ SELECT * FROM serialTest1; force | 100 (3 rows) +SELECT pg_get_serial_sequence('serialTest1', 'f2'); + pg_get_serial_sequence +--------------------------- + public.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 a7e7b157371..7886456a569 100644 --- a/src/test/regress/sql/identity.sql +++ b/src/test/regress/sql/identity.sql @@ -12,6 +12,8 @@ SELECT table_name, column_name, column_default, is_nullable, is_identity, identi -- internal sequences should not be shown here SELECT sequence_name FROM information_schema.sequences WHERE sequence_name LIKE 'itest%'; +SELECT pg_get_serial_sequence('itest1', 'a'); + CREATE TABLE itest4 (a int, b text); ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error, requires NOT NULL ALTER TABLE itest4 ALTER COLUMN a SET NOT NULL; diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql index b41c5a753dc..c50834a5b97 100644 --- a/src/test/regress/sql/sequence.sql +++ b/src/test/regress/sql/sequence.sql @@ -61,6 +61,8 @@ INSERT INTO serialTest1 VALUES ('wrong', NULL); SELECT * FROM serialTest1; +SELECT pg_get_serial_sequence('serialTest1', 'f2'); + -- test smallserial / bigserial CREATE TABLE serialTest2 (f1 text, f2 serial, f3 smallserial, f4 serial2, f5 bigserial, f6 serial8); |