Index: doc/src/sgml/func.sgml =================================================================== RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/func.sgml,v retrieving revision 1.209 diff -c -r1.209 func.sgml *** doc/src/sgml/func.sgml 16 Jun 2004 01:26:36 -0000 1.209 --- doc/src/sgml/func.sgml 21 Jun 2004 05:31:44 -0000 *************** *** 6376,6381 **** --- 6376,6387 ---- + To avoid referring to the sequence by name, the pg_get_serial_sequence + function exists. This function allows you to specify the table and column name of the serial or + bigserial column, instead of the sequence name itself. See . + + + The available sequence functions are: *************** *** 7228,7233 **** --- 7234,7243 ---- pg_get_userbyid + + pg_get_serial_sequence + + lists functions that extract information from the system catalogs. *************** *** 7251,7256 **** --- 7261,7269 ---- may be useful when examining the contents of system catalogs. pg_get_userbyid extracts a user's name given a user ID number. + The pg_get_serial_sequence + fetches the qualified name of the sequence on a serial or bigserial column, suitable + for passing to the . *************** *** 7334,7339 **** --- 7347,7358 ---- pg_get_userbyid(userid)nameget user name with given ID + + + pg_get_serial_sequence(table, column) + text + get schema-qualified and quoted name of the sequence that a serial or bigserial column depends upon. NULL is returned + if the column does not have a sequence attached Index: src/backend/utils/adt/ruleutils.c =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/ruleutils.c,v retrieving revision 1.173 diff -c -r1.173 ruleutils.c *** src/backend/utils/adt/ruleutils.c 18 Jun 2004 06:13:49 -0000 1.173 --- src/backend/utils/adt/ruleutils.c 21 Jun 2004 05:31:46 -0000 *************** *** 48,54 **** --- 48,57 ---- #include "catalog/namespace.h" #include "catalog/pg_cast.h" #include "catalog/pg_constraint.h" + #include "catalog/pg_depend.h" + #include "catalog/dependency.h" #include "catalog/pg_index.h" + #include "catalog/pg_namespace.h" #include "catalog/pg_opclass.h" #include "catalog/pg_operator.h" #include "catalog/pg_shadow.h" *************** *** 1093,1098 **** --- 1096,1226 ---- return buf.data; } + /* + * Implement a function for fetching the sequence details of a + * particular table and column, suitable for passing to setval, nextval + * or curval. + */ + Datum + pg_get_serial_sequence(PG_FUNCTION_ARGS) + { + text *tablein = PG_GETARG_TEXT_P(0); + text *columnin = PG_GETARG_TEXT_P(1); + char *column; + Oid tableOid; + Relation depRel; + ScanKeyData key[3]; + SysScanDesc scan; + HeapTuple tup, atttup; + Form_pg_depend deprec; + Form_pg_attribute attform; + StringInfoData buf; + Relation attrelation; + AttrNumber attnum; + + /* Find the OID of the table they are investigating */ + tableOid = RangeVarGetRelid(makeRangeVarFromNameList( + textToQualifiedNameList(tablein, "pg_get_serial_sequence")), false); + + /* Find the number of the column they are investigating */ + attrelation = heap_openr(AttributeRelationName, RowExclusiveLock); + + column = DatumGetCString(DirectFunctionCall1(textout, + PointerGetDatum(columnin))); + + atttup = SearchSysCacheAttName(tableOid, column); + + if (!HeapTupleIsValid(atttup)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("column \"%s\" does not exist", + column))); + attform = (Form_pg_attribute) GETSTRUCT(atttup); + + attnum = attform->attnum; + + /* Free up attribute tuple and relation */ + ReleaseSysCache(atttup); + heap_close(attrelation, RowExclusiveLock); + + /* Search the dependency table for the dependent sequence */ + depRel = heap_openr(DependRelationName, RowExclusiveLock); + + ScanKeyInit(&key[0], + Anum_pg_depend_refclassid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(RelOid_pg_class)); + ScanKeyInit(&key[1], + Anum_pg_depend_refobjid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(tableOid)); + ScanKeyInit(&key[2], + Anum_pg_depend_refobjsubid, + BTEqualStrategyNumber, F_INT4EQ, + Int16GetDatum(attnum)); + + scan = systable_beginscan(depRel, DependReferenceIndex, true, + SnapshotNow, 3, key); + + while (HeapTupleIsValid(tup = systable_getnext(scan))) + { + deprec = (Form_pg_depend) GETSTRUCT(tup); + + /* Check that we have found the serial sequence dependency */ + if (deprec->classid == RelOid_pg_class + && deprec->objsubid == 0 + && deprec->deptype == DEPENDENCY_INTERNAL) { + Relation relationRelation, nspRelation; + Form_pg_class classtuple; + Form_pg_namespace nsptuple; + HeapTuple tuple, nsptup; + Oid sequenceId = deprec->objid; + + /* Get the sequence relation */ + relationRelation = heap_openr(RelationRelationName, RowExclusiveLock); + tuple = SearchSysCache(RELOID, + ObjectIdGetDatum(sequenceId), + 0, 0, 0); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "cache lookup failed for relation %u", sequenceId); + classtuple = (Form_pg_class) GETSTRUCT(tuple); + + /* Get the namespace */ + nspRelation = heap_openr(NamespaceRelationName, RowExclusiveLock); + + nsptup = SearchSysCache(NAMESPACEOID, + ObjectIdGetDatum(classtuple->relnamespace), + 0, 0, 0); + if (!HeapTupleIsValid(nsptup)) + elog(ERROR, "cache lookup failed for namespace %u", classtuple->relnamespace); + nsptuple = (Form_pg_namespace) GETSTRUCT(nsptup); + + ReleaseSysCache(nsptup); + + heap_close(nspRelation, RowExclusiveLock); + + /* Initialise a buffer to construct the result string */ + initStringInfo(&buf); + + /* Append the sequence name */ + appendStringInfo(&buf, "%s.%s", + quote_identifier(NameStr(nsptuple->nspname)), quote_identifier(NameStr(classtuple->relname))); + + ReleaseSysCache(tuple); + heap_close(relationRelation, RowExclusiveLock); + systable_endscan(scan); + heap_close(depRel, RowExclusiveLock); + + PG_RETURN_TEXT_P(string_to_text(buf.data)); + } + } + + systable_endscan(scan); + + heap_close(depRel, RowExclusiveLock); + + PG_RETURN_NULL(); + } /* * Convert an int16[] Datum into a comma-separated list of column names Index: src/bin/pg_dump/pg_dump.c =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.c,v retrieving revision 1.375 diff -c -r1.375 pg_dump.c *** src/bin/pg_dump/pg_dump.c 18 Jun 2004 06:14:00 -0000 1.375 --- src/bin/pg_dump/pg_dump.c 21 Jun 2004 05:31:47 -0000 *************** *** 2346,2352 **** --- 2346,2354 ---- int i_relhasrules; int i_relhasoids; int i_owning_tab; + int i_owning_tab_name; int i_owning_col; + int i_owning_col_name; int i_reltablespace; /* Make sure we are in proper schema */ *************** *** 2381,2387 **** --- 2383,2391 ---- "relchecks, reltriggers, " "relhasindex, relhasrules, relhasoids, " "d.refobjid as owning_tab, " + "d.refobjid::regclass as owning_tab_name, " "d.refobjsubid as owning_col, " + "(SELECT attname FROM pg_attribute WHERE attrelid=d.refobjid AND attnum=d.refobjsubid) as owning_col_name, " "(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace " "from pg_class c " "left join pg_depend d on " *************** *** 2407,2413 **** --- 2411,2419 ---- "relchecks, reltriggers, " "relhasindex, relhasrules, relhasoids, " "d.refobjid as owning_tab, " + "d.refobjid::regclass as owning_tab_name, " "d.refobjsubid as owning_col, " + "(SELECT attname FROM pg_attribute WHERE attrelid=d.refobjid AND attnum=d.refobjsubid) as owning_col_name, " "NULL as reltablespace " "from pg_class c " "left join pg_depend d on " *************** *** 2429,2435 **** --- 2435,2443 ---- "relchecks, reltriggers, " "relhasindex, relhasrules, relhasoids, " "NULL::oid as owning_tab, " + "NULL::text as owning_tab_name, " "NULL::int4 as owning_col, " + "NULL::text as owning_col_name, " "NULL as reltablespace " "from pg_class " "where relkind in ('%c', '%c', '%c') " *************** *** 2447,2453 **** --- 2455,2463 ---- "relhasindex, relhasrules, " "'t'::bool as relhasoids, " "NULL::oid as owning_tab, " + "NULL::text as owning_tab_name, " "NULL::int4 as owning_col, " + "NULL::text as owning_col_name, " "NULL as reltablespace " "from pg_class " "where relkind in ('%c', '%c', '%c') " *************** *** 2475,2481 **** --- 2485,2493 ---- "relhasindex, relhasrules, " "'t'::bool as relhasoids, " "NULL::oid as owning_tab, " + "NULL::text as owning_tab_name, " "NULL::int4 as owning_col, " + "NULL::text as owning_col_name, " "NULL as reltablespace " "from pg_class c " "where relkind in ('%c', '%c') " *************** *** 2515,2521 **** --- 2527,2535 ---- i_relhasrules = PQfnumber(res, "relhasrules"); i_relhasoids = PQfnumber(res, "relhasoids"); i_owning_tab = PQfnumber(res, "owning_tab"); + i_owning_tab_name = PQfnumber(res, "owning_tab_name"); i_owning_col = PQfnumber(res, "owning_col"); + i_owning_col_name = PQfnumber(res, "owning_col_name"); i_reltablespace = PQfnumber(res, "reltablespace"); for (i = 0; i < ntups; i++) *************** *** 2538,2549 **** --- 2552,2567 ---- if (PQgetisnull(res, i, i_owning_tab)) { tblinfo[i].owning_tab = InvalidOid; + tblinfo[i].owning_tab_name = NULL; tblinfo[i].owning_col = 0; + tblinfo[i].owning_col_name = NULL; } else { tblinfo[i].owning_tab = atooid(PQgetvalue(res, i, i_owning_tab)); + tblinfo[i].owning_tab_name = strdup(PQgetvalue(res, i, i_owning_tab_name)); tblinfo[i].owning_col = atoi(PQgetvalue(res, i, i_owning_col)); + tblinfo[i].owning_col_name = strdup(PQgetvalue(res, i, i_owning_col_name)); } tblinfo[i].reltablespace = strdup(PQgetvalue(res, i, i_reltablespace)); *************** *** 7321,7327 **** { resetPQExpBuffer(query); appendPQExpBuffer(query, "SELECT pg_catalog.setval("); ! appendStringLiteral(query, fmtId(tbinfo->dobj.name), true); appendPQExpBuffer(query, ", %s, %s);\n", last, (called ? "true" : "false")); --- 7339,7359 ---- { resetPQExpBuffer(query); appendPQExpBuffer(query, "SELECT pg_catalog.setval("); ! /* ! * If this is a SERIAL sequence, then use the pg_get_serial_sequence ! * function to avoid hard-coding the sequence name ! */ ! if (OidIsValid(tbinfo->owning_tab)) ! { ! appendPQExpBuffer(query, "pg_catalog.pg_get_serial_sequence("); ! appendStringLiteral(query, tbinfo->owning_tab_name, true); ! appendPQExpBuffer(query, ", "); ! appendStringLiteral(query, tbinfo->owning_col_name, true); ! appendPQExpBuffer(query, ")"); ! } ! else { ! appendStringLiteral(query, fmtId(tbinfo->dobj.name), true); ! } appendPQExpBuffer(query, ", %s, %s);\n", last, (called ? "true" : "false")); Index: src/bin/pg_dump/pg_dump.h =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.h,v retrieving revision 1.109 diff -c -r1.109 pg_dump.h *** src/bin/pg_dump/pg_dump.h 18 Jun 2004 06:14:00 -0000 1.109 --- src/bin/pg_dump/pg_dump.h 21 Jun 2004 05:31:47 -0000 *************** *** 177,183 **** --- 177,185 ---- int ntrig; /* # of triggers */ /* these two are set only if table is a SERIAL column's sequence: */ Oid owning_tab; /* OID of table owning sequence */ + char *owning_tab_name; /* qualified, quoted name of table owning sequence */ int owning_col; /* attr # of column owning sequence */ + char *owning_col_name; /* name of column owning sequence */ bool interesting; /* true if need to collect more data */ bool dump; /* true if we want to dump it */ Index: src/include/catalog/pg_proc.h =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/include/catalog/pg_proc.h,v retrieving revision 1.338 diff -c -r1.338 pg_proc.h *** src/include/catalog/pg_proc.h 16 Jun 2004 01:26:49 -0000 1.338 --- src/include/catalog/pg_proc.h 21 Jun 2004 05:31:48 -0000 *************** *** 2234,2239 **** --- 2234,2241 ---- DESCR("index description"); DATA(insert OID = 1662 ( pg_get_triggerdef PGNSP PGUID 12 f f t f s 1 25 "26" _null_ pg_get_triggerdef - _null_ )); DESCR("trigger description"); + DATA(insert OID = 1665 ( pg_get_serial_sequence PGNSP PGUID 12 f f t f s 2 25 "25 25" _null_ pg_get_serial_sequence - _null_ )); + DESCR("name of sequence on a serial column"); DATA(insert OID = 1387 ( pg_get_constraintdef PGNSP PGUID 12 f f t f s 1 25 "26" _null_ pg_get_constraintdef - _null_ )); DESCR("constraint description"); DATA(insert OID = 1716 ( pg_get_expr PGNSP PGUID 12 f f t f s 2 25 "25 26" _null_ pg_get_expr - _null_ )); Index: src/include/utils/builtins.h =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/include/utils/builtins.h,v retrieving revision 1.243 diff -c -r1.243 builtins.h *** src/include/utils/builtins.h 13 Jun 2004 21:57:26 -0000 1.243 --- src/include/utils/builtins.h 21 Jun 2004 05:31:48 -0000 *************** *** 472,477 **** --- 472,478 ---- extern Datum pg_get_constraintdef(PG_FUNCTION_ARGS); extern Datum pg_get_constraintdef_ext(PG_FUNCTION_ARGS); extern char *pg_get_constraintdef_string(Oid constraintId); + extern Datum pg_get_serial_sequence(PG_FUNCTION_ARGS); extern Datum pg_get_userbyid(PG_FUNCTION_ARGS); extern Datum pg_get_expr(PG_FUNCTION_ARGS); extern Datum pg_get_expr_ext(PG_FUNCTION_ARGS); Index: src/test/regress/expected/sequence.out =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/test/regress/expected/sequence.out,v retrieving revision 1.6 diff -c -r1.6 sequence.out *** src/test/regress/expected/sequence.out 10 Jun 2004 17:56:01 -0000 1.6 --- src/test/regress/expected/sequence.out 21 Jun 2004 05:31:49 -0000 *************** *** 20,25 **** --- 20,43 ---- (3 rows) + SELECT nextval(pg_get_serial_sequence('public.serialTest', 'f2')); + nextval + --------- + 3 + (1 row) + + SELECT nextval(pg_get_serial_sequence('serialTest', 'f1')); + nextval + --------- + + (1 row) + + SELECT nextval(pg_get_serial_sequence('bad', 'col')); + ERROR: relation "bad" does not exist + SELECT nextval(pg_get_serial_sequence('serialTest', 'bad')); + ERROR: column "bad" does not exist + SELECT nextval(pg_get_serial_sequence('bad', 'bad')); + ERROR: relation "bad" does not exist CREATE SEQUENCE sequence_test; BEGIN; Index: src/test/regress/sql/sequence.sql =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/test/regress/sql/sequence.sql,v retrieving revision 1.2 diff -c -r1.2 sequence.sql *** src/test/regress/sql/sequence.sql 21 Nov 2003 22:32:49 -0000 1.2 --- src/test/regress/sql/sequence.sql 21 Jun 2004 05:31:49 -0000 *************** *** 11,16 **** --- 11,22 ---- SELECT * FROM serialTest; + SELECT nextval(pg_get_serial_sequence('public.serialTest', 'f2')); + SELECT nextval(pg_get_serial_sequence('serialTest', 'f1')); + SELECT nextval(pg_get_serial_sequence('bad', 'col')); + SELECT nextval(pg_get_serial_sequence('serialTest', 'bad')); + SELECT nextval(pg_get_serial_sequence('bad', 'bad')); + CREATE SEQUENCE sequence_test; BEGIN;