Skip to content

Commit a208ea7

Browse files
tglsfdctglsfdc
committed
Modify pg_dump to dump foreign-key constraints as constraints, not as
sets of triggers. Also modify psql \d command to show foreign key constraints as such and hide the triggers. pg_get_constraintdef() function added to backend to support these. From Rod Taylor, code review and some editorialization by Tom Lane.
1 parent 8dabef8 commit a208ea7

File tree

7 files changed

+421
-20
lines changed

7 files changed

+421
-20
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 18 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.111 2002/08/14 02:45:09 ishii Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.112 2002/08/16 23:01:18 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -5521,6 +5521,11 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
55215521
<entry><type>text</type></entry>
55225522
<entry>Get CREATE INDEX command for index</entry>
55235523
</row>
5524+
<row>
5525+
<entry><function>pg_get_constraintdef</function>(<parameter>constraintOID</parameter>)</entry>
5526+
<entry><type>text</type></entry>
5527+
<entry>Get definition of a constraint</entry>
5528+
</row>
55245529
<row>
55255530
<entry><function>pg_get_userbyid</function>(<parameter>userid</parameter>)</entry>
55265531
<entry><type>name</type></entry>
@@ -5542,16 +5547,25 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
55425547
<primary>pg_get_indexdef</primary>
55435548
</indexterm>
55445549

5550+
<indexterm zone="functions-misc">
5551+
<primary>pg_get_constraintdef</primary>
5552+
</indexterm>
5553+
55455554
<indexterm zone="functions-misc">
55465555
<primary>pg_get_userbyid</primary>
55475556
</indexterm>
55485557

55495558
<para>
55505559
These functions extract information from the system catalogs.
5551-
<function>pg_get_viewdef()</function>, <function>pg_get_ruledef()</function>, and
5552-
<function>pg_get_indexdef()</function> respectively reconstruct the creating
5553-
command for a view, rule, or index. (Note that this is a decompiled
5560+
<function>pg_get_viewdef()</function>,
5561+
<function>pg_get_ruledef()</function>,
5562+
<function>pg_get_indexdef()</function>, and
5563+
<function>pg_get_constraintdef()</function> respectively reconstruct the
5564+
creating command for a view, rule, index, or constraint.
5565+
(Note that this is a decompiled
55545566
reconstruction, not the verbatim text of the command.)
5567+
At present <function>pg_get_constraintdef()</function> only works for
5568+
foreign-key constraints.
55555569
<function>pg_get_userbyid()</function> extracts a user's name given a
55565570
<structfield>usesysid</structfield> value.
55575571
</para>

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

Lines changed: 217 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
* back to source text
44
*
55
* IDENTIFICATION
6-
* $Header: /cvsroot/pgsql/src/backend/utils/adt/ruleutils.c,v 1.115 2002/08/16 20:55:09 tgl Exp $
6+
* $Header: /cvsroot/pgsql/src/backend/utils/adt/ruleutils.c,v 1.116 2002/08/16 23:01:19 tgl Exp $
77
*
88
* This software is copyrighted by Jan Wieck - Hamburg.
99
*
@@ -40,10 +40,14 @@
4040
#include <unistd.h>
4141
#include <fcntl.h>
4242

43+
#include "access/genam.h"
44+
#include "catalog/catname.h"
4345
#include "catalog/heap.h"
4446
#include "catalog/index.h"
47+
#include "catalog/indexing.h"
4548
#include "catalog/namespace.h"
4649
#include "catalog/pg_cast.h"
50+
#include "catalog/pg_constraint.h"
4751
#include "catalog/pg_index.h"
4852
#include "catalog/pg_opclass.h"
4953
#include "catalog/pg_operator.h"
@@ -60,6 +64,8 @@
6064
#include "parser/parsetree.h"
6165
#include "rewrite/rewriteManip.h"
6266
#include "rewrite/rewriteSupport.h"
67+
#include "utils/array.h"
68+
#include "utils/fmgroids.h"
6369
#include "utils/lsyscache.h"
6470

6571

@@ -116,6 +122,8 @@ static char *query_getviewrule = "SELECT * FROM pg_catalog.pg_rewrite WHERE ev_c
116122
* ----------
117123
*/
118124
static text *pg_do_getviewdef(Oid viewoid);
125+
static void decompile_column_index_array(Datum column_index_array, Oid relId,
126+
StringInfo buf);
119127
static void make_ruledef(StringInfo buf, HeapTuple ruletup, TupleDesc rulettc);
120128
static void make_viewdef(StringInfo buf, HeapTuple ruletup, TupleDesc rulettc);
121129
static void get_query_def(Query *query, StringInfo buf, List *parentnamespace,
@@ -528,6 +536,214 @@ pg_get_indexdef(PG_FUNCTION_ARGS)
528536
}
529537

530538

539+
/*
540+
* pg_get_constraintdef
541+
*
542+
* Returns the definition for the constraint, ie, everything that needs to
543+
* appear after "ALTER TABLE ... ADD CONSTRAINT <constraintname>".
544+
*
545+
* XXX The present implementation only works for foreign-key constraints, but
546+
* it could and should handle anything pg_constraint stores.
547+
*/
548+
Datum
549+
pg_get_constraintdef(PG_FUNCTION_ARGS)
550+
{
551+
Oid constraintId = PG_GETARG_OID(0);
552+
text *result;
553+
StringInfoData buf;
554+
int len;
555+
Relation conDesc;
556+
SysScanDesc conscan;
557+
ScanKeyData skey[1];
558+
HeapTuple tup;
559+
Form_pg_constraint conForm;
560+
561+
/*
562+
* Fetch the pg_constraint row. There's no syscache for pg_constraint
563+
* so we must do it the hard way.
564+
*/
565+
conDesc = heap_openr(ConstraintRelationName, AccessShareLock);
566+
567+
ScanKeyEntryInitialize(&skey[0], 0x0,
568+
ObjectIdAttributeNumber, F_OIDEQ,
569+
ObjectIdGetDatum(constraintId));
570+
571+
conscan = systable_beginscan(conDesc, ConstraintOidIndex, true,
572+
SnapshotNow, 1, skey);
573+
574+
tup = systable_getnext(conscan);
575+
if (!HeapTupleIsValid(tup))
576+
elog(ERROR, "Failed to find constraint with OID %u", constraintId);
577+
conForm = (Form_pg_constraint) GETSTRUCT(tup);
578+
579+
initStringInfo(&buf);
580+
581+
switch (conForm->contype)
582+
{
583+
case CONSTRAINT_FOREIGN:
584+
{
585+
Datum val;
586+
bool isnull;
587+
const char *string;
588+
589+
/* Start off the constraint definition */
590+
appendStringInfo(&buf, "FOREIGN KEY (");
591+
592+
/* Fetch and build referencing-column list */
593+
val = heap_getattr(tup, Anum_pg_constraint_conkey,
594+
RelationGetDescr(conDesc), &isnull);
595+
if (isnull)
596+
elog(ERROR, "pg_get_constraintdef: Null conkey for constraint %u",
597+
constraintId);
598+
599+
decompile_column_index_array(val, conForm->conrelid, &buf);
600+
601+
/* add foreign relation name */
602+
appendStringInfo(&buf, ") REFERENCES %s(",
603+
generate_relation_name(conForm->confrelid));
604+
605+
/* Fetch and build referenced-column list */
606+
val = heap_getattr(tup, Anum_pg_constraint_confkey,
607+
RelationGetDescr(conDesc), &isnull);
608+
if (isnull)
609+
elog(ERROR, "pg_get_constraintdef: Null confkey for constraint %u",
610+
constraintId);
611+
612+
decompile_column_index_array(val, conForm->confrelid, &buf);
613+
614+
appendStringInfo(&buf, ")");
615+
616+
/* Add match type */
617+
switch (conForm->confmatchtype)
618+
{
619+
case FKCONSTR_MATCH_FULL:
620+
string = " MATCH FULL";
621+
break;
622+
case FKCONSTR_MATCH_PARTIAL:
623+
string = " MATCH PARTIAL";
624+
break;
625+
case FKCONSTR_MATCH_UNSPECIFIED:
626+
string = "";
627+
break;
628+
default:
629+
elog(ERROR, "pg_get_constraintdef: Unknown confmatchtype '%c' for constraint %u",
630+
conForm->confmatchtype, constraintId);
631+
string = ""; /* keep compiler quiet */
632+
break;
633+
}
634+
appendStringInfo(&buf, "%s", string);
635+
636+
/* Add ON UPDATE and ON DELETE clauses */
637+
switch (conForm->confupdtype)
638+
{
639+
case FKCONSTR_ACTION_NOACTION:
640+
string = "NO ACTION";
641+
break;
642+
case FKCONSTR_ACTION_RESTRICT:
643+
string = "RESTRICT";
644+
break;
645+
case FKCONSTR_ACTION_CASCADE:
646+
string = "CASCADE";
647+
break;
648+
case FKCONSTR_ACTION_SETNULL:
649+
string = "SET NULL";
650+
break;
651+
case FKCONSTR_ACTION_SETDEFAULT:
652+
string = "SET DEFAULT";
653+
break;
654+
default:
655+
elog(ERROR, "pg_get_constraintdef: Unknown confupdtype '%c' for constraint %u",
656+
conForm->confupdtype, constraintId);
657+
string = ""; /* keep compiler quiet */
658+
break;
659+
}
660+
appendStringInfo(&buf, " ON UPDATE %s", string);
661+
662+
switch (conForm->confdeltype)
663+
{
664+
case FKCONSTR_ACTION_NOACTION:
665+
string = "NO ACTION";
666+
break;
667+
case FKCONSTR_ACTION_RESTRICT:
668+
string = "RESTRICT";
669+
break;
670+
case FKCONSTR_ACTION_CASCADE:
671+
string = "CASCADE";
672+
break;
673+
case FKCONSTR_ACTION_SETNULL:
674+
string = "SET NULL";
675+
break;
676+
case FKCONSTR_ACTION_SETDEFAULT:
677+
string = "SET DEFAULT";
678+
break;
679+
default:
680+
elog(ERROR, "pg_get_constraintdef: Unknown confdeltype '%c' for constraint %u",
681+
conForm->confdeltype, constraintId);
682+
string = ""; /* keep compiler quiet */
683+
break;
684+
}
685+
appendStringInfo(&buf, " ON DELETE %s", string);
686+
687+
break;
688+
}
689+
690+
/*
691+
* XXX Add more code here for other contypes
692+
*/
693+
default:
694+
elog(ERROR, "pg_get_constraintdef: unsupported constraint type '%c'",
695+
conForm->contype);
696+
break;
697+
}
698+
699+
/* Record the results */
700+
len = buf.len + VARHDRSZ;
701+
result = (text *) palloc(len);
702+
VARATT_SIZEP(result) = len;
703+
memcpy(VARDATA(result), buf.data, buf.len);
704+
705+
/* Cleanup */
706+
pfree(buf.data);
707+
systable_endscan(conscan);
708+
heap_close(conDesc, AccessShareLock);
709+
710+
PG_RETURN_TEXT_P(result);
711+
}
712+
713+
714+
/*
715+
* Convert an int16[] Datum into a comma-separated list of column names
716+
* for the indicated relation; append the list to buf.
717+
*/
718+
static void
719+
decompile_column_index_array(Datum column_index_array, Oid relId,
720+
StringInfo buf)
721+
{
722+
Datum *keys;
723+
int nKeys;
724+
int j;
725+
726+
/* Extract data from array of int16 */
727+
deconstruct_array(DatumGetArrayTypeP(column_index_array),
728+
true, 2, 's',
729+
&keys, &nKeys);
730+
731+
for (j = 0; j < nKeys; j++)
732+
{
733+
char *colName;
734+
735+
colName = get_attname(relId, DatumGetInt16(keys[j]));
736+
737+
if (j == 0)
738+
appendStringInfo(buf, "%s",
739+
quote_identifier(colName));
740+
else
741+
appendStringInfo(buf, ", %s",
742+
quote_identifier(colName));
743+
}
744+
}
745+
746+
531747
/* ----------
532748
* get_expr - Decompile an expression tree
533749
*

0 commit comments

Comments
 (0)