Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita0e842d

Browse files
committed
Add pg_get_serial_sequence() function, and cause pg_dump to use it.
This eliminates the assumption that a serial column's sequence willhave the same name on reload that it was given in the original database.Christopher Kings-Lynne
1 parentef28802 commita0e842d

File tree

6 files changed

+185
-29
lines changed

6 files changed

+185
-29
lines changed

‎doc/src/sgml/func.sgml‎

Lines changed: 44 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.210 2004/06/24 19:57:14 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.211 2004/06/25 17:20:21 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -7228,29 +7228,13 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
72287228
<primary>pg_get_userbyid</primary>
72297229
</indexterm>
72307230

7231+
<indexterm zone="functions-misc">
7232+
<primary>pg_get_serial_sequence</primary>
7233+
</indexterm>
7234+
72317235
<para>
72327236
<xref linkend="functions-misc-catalog-table"> lists functions that
72337237
extract information from the system catalogs.
7234-
<function>pg_get_viewdef</function>,
7235-
<function>pg_get_ruledef</function>,
7236-
<function>pg_get_indexdef</function>,
7237-
<function>pg_get_triggerdef</function>, and
7238-
<function>pg_get_constraintdef</function> respectively
7239-
reconstruct the creating command for a view, rule, index, trigger, or
7240-
constraint. (Note that this is a decompiled reconstruction, not
7241-
the original text of the command.) Most of these come in two
7242-
variants, one of which can optionally <quote>pretty-print</> the result.
7243-
The pretty-printed format is more readable, but the default format is more
7244-
likely to be
7245-
interpreted the same way by future versions of <productname>PostgreSQL</>;
7246-
avoid using pretty-printed output for dump purposes.
7247-
Passing <literal>false</> for the pretty-print parameter yields the
7248-
same result as the variant that does not have the parameter at all.
7249-
<function>pg_get_expr</function> decompiles the internal form of an
7250-
individual expression, such as the default value for a column. It
7251-
may be useful when examining the contents of system catalogs.
7252-
<function>pg_get_userbyid</function>
7253-
extracts a user's name given a user ID number.
72547238
</para>
72557239

72567240
<table id="functions-misc-catalog-table">
@@ -7335,10 +7319,49 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
73357319
<entry><type>name</type></entry>
73367320
<entry>get user name with given ID</entry>
73377321
</row>
7322+
<row>
7323+
<entry><literal><function>pg_get_serial_sequence</function>(<parameter>table_name</parameter>, <parameter>column_name</parameter>)</literal></entry>
7324+
<entry><type>text</type></entry>
7325+
<entry>get name of the sequence that a serial or bigserial column
7326+
uses</entry>
7327+
</row>
73387328
</tbody>
73397329
</tgroup>
73407330
</table>
73417331

7332+
<para>
7333+
<function>pg_get_viewdef</function>,
7334+
<function>pg_get_ruledef</function>,
7335+
<function>pg_get_indexdef</function>,
7336+
<function>pg_get_triggerdef</function>, and
7337+
<function>pg_get_constraintdef</function> respectively
7338+
reconstruct the creating command for a view, rule, index, trigger, or
7339+
constraint. (Note that this is a decompiled reconstruction, not
7340+
the original text of the command.)
7341+
<function>pg_get_expr</function> decompiles the internal form of an
7342+
individual expression, such as the default value for a column. It
7343+
may be useful when examining the contents of system catalogs.
7344+
Most of these functions come in two
7345+
variants, one of which can optionally <quote>pretty-print</> the result.
7346+
The pretty-printed format is more readable, but the default format is more
7347+
likely to be
7348+
interpreted the same way by future versions of <productname>PostgreSQL</>;
7349+
avoid using pretty-printed output for dump purposes.
7350+
Passing <literal>false</> for the pretty-print parameter yields the
7351+
same result as the variant that does not have the parameter at all.
7352+
</para>
7353+
7354+
<para>
7355+
<function>pg_get_userbyid</function>
7356+
extracts a user's name given a user ID number.
7357+
<function>pg_get_serial_sequence</function>
7358+
fetches the name of the sequence associated with a serial or
7359+
bigserial column. The name is suitably formatted
7360+
for passing to the sequence functions (see <xref
7361+
linkend="functions-sequence">).
7362+
NULL is returned if the column does not have a sequence attached.
7363+
</para>
7364+
73427365
<indexterm zone="functions-misc">
73437366
<primary>obj_description</primary>
73447367
</indexterm>

‎src/backend/utils/adt/ruleutils.c‎

Lines changed: 113 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
*back to source text
44
*
55
* IDENTIFICATION
6-
* $PostgreSQL: pgsql/src/backend/utils/adt/ruleutils.c,v 1.173 2004/06/18 06:13:49 tgl Exp $
6+
* $PostgreSQL: pgsql/src/backend/utils/adt/ruleutils.c,v 1.174 2004/06/25 17:20:24 tgl Exp $
77
*
88
* This software is copyrighted by Jan Wieck - Hamburg.
99
*
@@ -42,12 +42,14 @@
4242

4343
#include"access/genam.h"
4444
#include"catalog/catname.h"
45+
#include"catalog/dependency.h"
4546
#include"catalog/heap.h"
4647
#include"catalog/index.h"
4748
#include"catalog/indexing.h"
4849
#include"catalog/namespace.h"
4950
#include"catalog/pg_cast.h"
5051
#include"catalog/pg_constraint.h"
52+
#include"catalog/pg_depend.h"
5153
#include"catalog/pg_index.h"
5254
#include"catalog/pg_opclass.h"
5355
#include"catalog/pg_operator.h"
@@ -1232,6 +1234,116 @@ pg_get_userbyid(PG_FUNCTION_ARGS)
12321234
PG_RETURN_NAME(result);
12331235
}
12341236

1237+
1238+
/*
1239+
* pg_get_serial_sequence
1240+
*Get the name of the sequence used by a serial column,
1241+
*formatted suitably for passing to setval, nextval or currval.
1242+
*/
1243+
Datum
1244+
pg_get_serial_sequence(PG_FUNCTION_ARGS)
1245+
{
1246+
text*tablename=PG_GETARG_TEXT_P(0);
1247+
text*columnname=PG_GETARG_TEXT_P(1);
1248+
RangeVar*tablerv;
1249+
OidtableOid;
1250+
char*column;
1251+
AttrNumberattnum;
1252+
OidsequenceId=InvalidOid;
1253+
RelationdepRel;
1254+
ScanKeyDatakey[3];
1255+
SysScanDescscan;
1256+
HeapTupletup;
1257+
1258+
/* Get the OID of the table */
1259+
tablerv=makeRangeVarFromNameList(textToQualifiedNameList(tablename,
1260+
"pg_get_serial_sequence"));
1261+
tableOid=RangeVarGetRelid(tablerv, false);
1262+
1263+
/* Get the number of the column */
1264+
column=DatumGetCString(DirectFunctionCall1(textout,
1265+
PointerGetDatum(columnname)));
1266+
1267+
attnum=get_attnum(tableOid,column);
1268+
if (attnum==InvalidAttrNumber)
1269+
ereport(ERROR,
1270+
(errcode(ERRCODE_UNDEFINED_COLUMN),
1271+
errmsg("column \"%s\" of relation \"%s\" does not exist",
1272+
column,tablerv->relname)));
1273+
1274+
/* Search the dependency table for the dependent sequence */
1275+
depRel=heap_openr(DependRelationName,AccessShareLock);
1276+
1277+
ScanKeyInit(&key[0],
1278+
Anum_pg_depend_refclassid,
1279+
BTEqualStrategyNumber,F_OIDEQ,
1280+
ObjectIdGetDatum(RelOid_pg_class));
1281+
ScanKeyInit(&key[1],
1282+
Anum_pg_depend_refobjid,
1283+
BTEqualStrategyNumber,F_OIDEQ,
1284+
ObjectIdGetDatum(tableOid));
1285+
ScanKeyInit(&key[2],
1286+
Anum_pg_depend_refobjsubid,
1287+
BTEqualStrategyNumber,F_INT4EQ,
1288+
Int32GetDatum(attnum));
1289+
1290+
scan=systable_beginscan(depRel,DependReferenceIndex, true,
1291+
SnapshotNow,3,key);
1292+
1293+
while (HeapTupleIsValid(tup=systable_getnext(scan)))
1294+
{
1295+
Form_pg_dependdeprec= (Form_pg_depend)GETSTRUCT(tup);
1296+
1297+
/*
1298+
* We assume any internal dependency of a relation on a column
1299+
* must be what we are looking for.
1300+
*/
1301+
if (deprec->classid==RelOid_pg_class&&
1302+
deprec->objsubid==0&&
1303+
deprec->deptype==DEPENDENCY_INTERNAL)
1304+
{
1305+
sequenceId=deprec->objid;
1306+
break;
1307+
}
1308+
}
1309+
1310+
systable_endscan(scan);
1311+
heap_close(depRel,AccessShareLock);
1312+
1313+
if (OidIsValid(sequenceId))
1314+
{
1315+
HeapTupleclasstup;
1316+
Form_pg_classclasstuple;
1317+
char*nspname;
1318+
char*result;
1319+
1320+
/* Get the sequence's pg_class entry */
1321+
classtup=SearchSysCache(RELOID,
1322+
ObjectIdGetDatum(sequenceId),
1323+
0,0,0);
1324+
if (!HeapTupleIsValid(classtup))
1325+
elog(ERROR,"cache lookup failed for relation %u",sequenceId);
1326+
classtuple= (Form_pg_class)GETSTRUCT(classtup);
1327+
1328+
/* Get the namespace */
1329+
nspname=get_namespace_name(classtuple->relnamespace);
1330+
if (!nspname)
1331+
elog(ERROR,"cache lookup failed for namespace %u",
1332+
classtuple->relnamespace);
1333+
1334+
/* And construct the result string */
1335+
result=quote_qualified_identifier(nspname,
1336+
NameStr(classtuple->relname));
1337+
1338+
ReleaseSysCache(classtup);
1339+
1340+
PG_RETURN_TEXT_P(string_to_text(result));
1341+
}
1342+
1343+
PG_RETURN_NULL();
1344+
}
1345+
1346+
12351347
/* ----------
12361348
* deparse_expression- General utility for deparsing expressions
12371349
*

‎src/bin/pg_dump/pg_dump.c‎

Lines changed: 20 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -12,7 +12,7 @@
1212
*by PostgreSQL
1313
*
1414
* IDENTIFICATION
15-
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.376 2004/06/21 13:36:41 tgl Exp $
15+
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.377 2004/06/25 17:20:26 tgl Exp $
1616
*
1717
*-------------------------------------------------------------------------
1818
*/
@@ -7319,9 +7319,27 @@ dumpSequence(Archive *fout, TableInfo *tbinfo)
73197319

73207320
if (!schemaOnly)
73217321
{
7322+
TableInfo*owning_tab;
7323+
73227324
resetPQExpBuffer(query);
73237325
appendPQExpBuffer(query,"SELECT pg_catalog.setval(");
7324-
appendStringLiteral(query,fmtId(tbinfo->dobj.name), true);
7326+
/*
7327+
* If this is a SERIAL sequence, then use the pg_get_serial_sequence
7328+
* function to avoid hard-coding the sequence name. Note that this
7329+
* implicitly assumes that the sequence and its owning table are in
7330+
* the same schema, because we don't schema-qualify the reference.
7331+
*/
7332+
if (OidIsValid(tbinfo->owning_tab)&&
7333+
(owning_tab=findTableByOid(tbinfo->owning_tab))!=NULL)
7334+
{
7335+
appendPQExpBuffer(query,"pg_catalog.pg_get_serial_sequence(");
7336+
appendStringLiteral(query,fmtId(owning_tab->dobj.name), true);
7337+
appendPQExpBuffer(query,", ");
7338+
appendStringLiteral(query,owning_tab->attnames[tbinfo->owning_col-1], true);
7339+
appendPQExpBuffer(query,")");
7340+
}
7341+
else
7342+
appendStringLiteral(query,fmtId(tbinfo->dobj.name), true);
73257343
appendPQExpBuffer(query,", %s, %s);\n",
73267344
last, (called ?"true" :"false"));
73277345

‎src/include/catalog/catversion.h‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.239 2004/06/21 04:06:07 tgl Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.240 2004/06/25 17:20:28 tgl Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO200406202
56+
#defineCATALOG_VERSION_NO200406251
5757

5858
#endif

‎src/include/catalog/pg_proc.h‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.338 2004/06/16 01:26:49 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.339 2004/06/25 17:20:28 tgl Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -2238,6 +2238,8 @@ DATA(insert OID = 1387 ( pg_get_constraintdef PGNSP PGUID 12 f f t f s 1 25 "26
22382238
DESCR("constraint description");
22392239
DATA(insertOID=1716 (pg_get_exprPGNSPPGUID12fftfs225"25 26"_null_pg_get_expr-_null_ ));
22402240
DESCR("deparse an encoded expression");
2241+
DATA(insertOID=1665 (pg_get_serial_sequencePGNSPPGUID12fftfs225"25 25"_null_pg_get_serial_sequence-_null_ ));
2242+
DESCR("name of sequence for a serial column");
22412243

22422244

22432245
/* Generic referential integrity constraint triggers */

‎src/include/utils/builtins.h‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.243 2004/06/13 21:57:26 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.244 2004/06/25 17:20:29 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -472,9 +472,10 @@ extern Datum pg_get_triggerdef(PG_FUNCTION_ARGS);
472472
externDatumpg_get_constraintdef(PG_FUNCTION_ARGS);
473473
externDatumpg_get_constraintdef_ext(PG_FUNCTION_ARGS);
474474
externchar*pg_get_constraintdef_string(OidconstraintId);
475-
externDatumpg_get_userbyid(PG_FUNCTION_ARGS);
476475
externDatumpg_get_expr(PG_FUNCTION_ARGS);
477476
externDatumpg_get_expr_ext(PG_FUNCTION_ARGS);
477+
externDatumpg_get_userbyid(PG_FUNCTION_ARGS);
478+
externDatumpg_get_serial_sequence(PG_FUNCTION_ARGS);
478479
externchar*deparse_expression(Node*expr,List*dpcontext,
479480
boolforceprefix,boolshowimplicit);
480481
externList*deparse_context_for(constchar*aliasname,Oidrelid);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp