Attached is a patch to add a couple basic dependency look-up capability
functions. They're based off the pg_get_serial_sequence function, and
are kind of the inverse of that function in some respects.
The patch adds two new functions to the backend, pg_get_owner_object and
pg_get_owner_column. These look up the requested object in the
pg_depend table, looking for an 'a' type dependency to another relation,
and resolve either the relation or column names to text.
postgres=# SELECT pg_get_owner_object('tbl_id_seq') AS obj,
postgres-# pg_get_owner_column('tbl_id_seq') AS col,
postgres-# pg_get_serial_sequence(
postgres(# pg_get_owner_object('tbl_id_seq'),
postgres(# pg_get_owner_column('tbl_id_seq')
postgres(# ) AS full_circle;
obj | col | full_circle
------------+-----+-------------------
public.tbl | id | public.tbl_id_seq
(1 row)
I tried not to be too myopic in the design, but apart from sequence
ownership I can't really think of any other uses for this. 'p'in and
'i'nternal relationships wouldn't make much sense, and 'n'ormal ones are
generally exposed in other ways. Anyone have any input there on how
this could be expanded?
Anyway, as an immediate practical example the patch modifies psql's
describe-verbose on sequences to show the ownership information...
postgres=# \d+ tbl_id_seq
(...)
Owner: public.tbl.id
- Josh Williams
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.487
diff -c -r1.487 func.sgml
*** doc/src/sgml/func.sgml 16 Aug 2009 19:55:21 -0000 1.487
--- doc/src/sgml/func.sgml 2 Sep 2009 23:11:15 -0000
***************
*** 12264,12269 ****
--- 12264,12277 ----
</indexterm>
<indexterm>
+ <primary>pg_get_owner_object</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>pg_get_owner_column</primary>
+ </indexterm>
+
+ <indexterm>
<primary>pg_tablespace_databases</primary>
</indexterm>
***************
*** 12365,12370 ****
--- 12373,12388 ----
uses</entry>
</row>
<row>
+ <entry><function>pg_get_owner_object</function>(<parameter>relation_oid</parameter>)</entry>
+ <entry><type>text</type></entry>
+ <entry>get name of the relation that owns the specified object, such as a sequence</entry>
+ </row>
+ <row>
+ <entry><function>pg_get_owner_column</function>(<parameter>relation_oid</parameter>)</entry>
+ <entry><type>text</type></entry>
+ <entry>get column name associated with the specified object in its owning relation</entry>
+ </row>
+ <row>
<entry><function>pg_get_triggerdef</function>(<parameter>trigger_oid</parameter>)</entry>
<entry><type>text</type></entry>
<entry>get <command>CREATE [ CONSTRAINT ] TRIGGER</> command for trigger</entry>
***************
*** 12478,12483 ****
--- 12496,12513 ----
</para>
<para>
+ <function>pg_get_owner_object</function> returns the name of the relation
+ that owns the specified relation object, or NULL if the object isn't owned
+ by a relation. The input parameter can be passed as an OID or possibly a
+ double-quoted identifier. This can be treated in some respects as the
+ inverse of <function>pg_get_serial_sequence</function>, where the association
+ can be modified or removed with <command>ALTER SEQUENCE OWNED BY</>.
+ <function>pg_get_owner_column</function> returns the name of the column
+ associated with an owned object, such as the name of a sequence's
+ original <type>serial</> column.
+ </para>
+
+ <para>
<function>pg_get_userbyid</function> extracts a role's name given
its OID.
</para>
Index: src/backend/utils/adt/ruleutils.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/ruleutils.c,v
retrieving revision 1.306
diff -c -r1.306 ruleutils.c
*** src/backend/utils/adt/ruleutils.c 1 Aug 2009 19:59:41 -0000 1.306
--- src/backend/utils/adt/ruleutils.c 2 Sep 2009 23:11:19 -0000
***************
*** 1446,1451 ****
--- 1446,1601 ----
/*
+ * pg_get_owner_object
+ * Returns the name of the object that owns the specified object
+ * by looking up an "auto" dependency relationship.
+ * Useful for finding a sequence's parent table.
+ * See pg_get_owner_column for the originating serial column.
+ */
+ Datum
+ pg_get_owner_object(PG_FUNCTION_ARGS)
+ {
+ Oid relId = PG_GETARG_OID(0);
+ Oid ownerId = InvalidOid;
+ Relation depRel;
+ ScanKeyData key[3];
+ SysScanDesc depScan;
+ HeapTuple tup;
+
+ /* Find the requested object in the dependency table... */
+ depRel = heap_open(DependRelationId, AccessShareLock);
+
+ ScanKeyInit(&key[0],
+ Anum_pg_depend_classid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationRelationId));
+ ScanKeyInit(&key[1],
+ Anum_pg_depend_objid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(relId));
+ ScanKeyInit(&key[2],
+ Anum_pg_depend_objsubid,
+ BTEqualStrategyNumber, F_INT4EQ,
+ Int32GetDatum(0));
+
+ depScan = systable_beginscan(depRel, DependDependerIndexId, true, SnapshotNow, 3, key);
+
+ while (HeapTupleIsValid(tup = systable_getnext(depScan)))
+ {
+ Form_pg_depend depRecord = (Form_pg_depend) GETSTRUCT(tup);
+
+ /* ... and look for the relation it depends on. */
+ if (depRecord->refclassid == RelationRelationId &&
+ depRecord->deptype == DEPENDENCY_AUTO &&
+ get_rel_relkind(depRecord->refobjid) == RELKIND_RELATION)
+ {
+ ownerId = depRecord->refobjid;
+ break;
+ }
+ }
+
+ systable_endscan(depScan);
+ heap_close(depRel, AccessShareLock);
+
+ if (OidIsValid(ownerId))
+ {
+ Form_pg_class classRecord;
+ char *nspName;
+ char *result;
+
+ /* Get the relation's pg_class entry */
+ tup = SearchSysCache(RELOID, ObjectIdGetDatum(ownerId), 0, 0, 0);
+
+ if (!HeapTupleIsValid(tup))
+ elog(ERROR, "cache lookup failed for relation %u", ownerId);
+ classRecord = (Form_pg_class) GETSTRUCT(tup);
+
+ /* Get the namespace */
+ nspName = get_namespace_name(classRecord->relnamespace);
+ if (!nspName)
+ elog(ERROR, "cache lookup failed for namespace %u", classRecord->relnamespace);
+
+ /* And construct the result string */
+ result = quote_qualified_identifier(nspName, NameStr(classRecord->relname));
+
+ ReleaseSysCache(tup);
+
+ PG_RETURN_TEXT_P(string_to_text(result));
+ }
+
+ /* No object was found */
+ PG_RETURN_NULL();
+ }
+
+
+ /*
+ * pg_get_owner_column
+ * Returns the column name, if available, on the object that
+ * owns the specified object. Similar to pg_get_owner_object.
+ * Useful for finding a sequence's parent serial column.
+ */
+ Datum
+ pg_get_owner_column(PG_FUNCTION_ARGS)
+ {
+ Oid relId = PG_GETARG_OID(0);
+ Oid ownerId = InvalidOid;
+ AttrNumber columnAttNum = 0;
+ Relation depRel;
+ ScanKeyData key[3];
+ SysScanDesc depScan;
+ HeapTuple tup;
+
+ /* Find the requested object in the dependency table... */
+ depRel = heap_open(DependRelationId, AccessShareLock);
+
+ ScanKeyInit(&key[0],
+ Anum_pg_depend_classid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationRelationId));
+ ScanKeyInit(&key[1],
+ Anum_pg_depend_objid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(relId));
+ ScanKeyInit(&key[2],
+ Anum_pg_depend_objsubid,
+ BTEqualStrategyNumber, F_INT4EQ,
+ Int32GetDatum(0));
+
+ depScan = systable_beginscan(depRel, DependDependerIndexId, true, SnapshotNow, 3, key);
+
+ while (HeapTupleIsValid(tup = systable_getnext(depScan)))
+ {
+ Form_pg_depend depRecord = (Form_pg_depend) GETSTRUCT(tup);
+
+ /* ... and look for the relation it depends on. */
+ if (depRecord->refclassid == RelationRelationId &&
+ depRecord->deptype == DEPENDENCY_AUTO &&
+ get_rel_relkind(depRecord->refobjid) == RELKIND_RELATION)
+ {
+ ownerId = depRecord->refobjid;
+ columnAttNum = (AttrNumber) depRecord->refobjsubid;
+ break;
+ }
+ }
+
+ systable_endscan(depScan);
+ heap_close(depRel, AccessShareLock);
+
+ if (OidIsValid(ownerId) && columnAttNum > 0)
+ {
+ char *result;
+
+ result = get_attname(ownerId, columnAttNum);
+
+ PG_RETURN_TEXT_P(string_to_text(result));
+ }
+
+ /* No object/column was found */
+ PG_RETURN_NULL();
+ }
+
+
+ /*
* pg_get_functiondef
* Returns the complete "CREATE OR REPLACE FUNCTION ..." statement for
* the specified function.
Index: src/bin/psql/describe.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.226
diff -c -r1.226 describe.c
*** src/bin/psql/describe.c 29 Jul 2009 20:56:19 -0000 1.226
--- src/bin/psql/describe.c 2 Sep 2009 23:11:21 -0000
***************
*** 1444,1449 ****
--- 1444,1479 ----
PQclear(result);
}
}
+ else if (tableinfo.relkind == 'S')
+ {
+ /* Footer information about a Sequence */
+
+ PGresult *result = NULL;
+ if (verbose)
+ {
+ const char *owner = _("Owner:");
+
+ printfPQExpBuffer(&buf, "SELECT pg_catalog.pg_get_owner_object(%s), "
+ "pg_catalog.pg_get_owner_column(%s)",
+ oid, oid);
+ result = PSQLexec(buf.data, false);
+ if (!result)
+ goto error_return;
+
+ if (!PQgetisnull(result, 0, 0))
+ {
+ printfPQExpBuffer(&buf, "%s %s.%s",
+ owner,
+ PQgetvalue(result, 0, 0),
+ PQgetvalue(result, 0, 1));
+ }
+ else
+ {
+ printfPQExpBuffer(&buf, "%s None", owner);
+ }
+ printTableAddFooter(&cont, buf.data);
+ }
+ }
else if (tableinfo.relkind == 'r')
{
/* Footer information about a table */
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.550
diff -c -r1.550 pg_proc.h
*** src/include/catalog/pg_proc.h 1 Sep 2009 02:54:52 -0000 1.550
--- src/include/catalog/pg_proc.h 2 Sep 2009 23:11:28 -0000
***************
*** 2311,2316 ****
--- 2311,2321 ----
DATA(insert OID = 2165 ( pg_get_function_result PGNSP PGUID 12 1 0 0 f f f t f s 1 0 25 "26" _null_ _null_ _null_ _null_ pg_get_function_result _null_ _null_ _null_ ));
DESCR("result type of a function");
+ DATA(insert OID = 2336 ( pg_get_owner_object PGNSP PGUID 12 1 0 0 f f f t f s 1 0 25 "2205" _null_ _null_ _null_ _null_ pg_get_owner_object _null_ _null_ _null_ ));
+ DESCR("name of the object owning a specified object");
+ DATA(insert OID = 2337 ( pg_get_owner_column PGNSP PGUID 12 1 0 0 f f f t f s 1 0 25 "2205" _null_ _null_ _null_ _null_ pg_get_owner_column _null_ _null_ _null_ ));
+ DESCR("name of the column owning a specified object");
+
DATA(insert OID = 1686 ( pg_get_keywords PGNSP PGUID 12 10 400 0 f f f t t s 0 0 2249 "" "{25,18,25}" "{o,o,o}" "{word,catcode,catdesc}" _null_ pg_get_keywords _null_ _null_ _null_ ));
DESCR("list of SQL keywords");
Index: src/include/utils/builtins.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/utils/builtins.h,v
retrieving revision 1.338
diff -c -r1.338 builtins.h
*** src/include/utils/builtins.h 4 Aug 2009 16:08:36 -0000 1.338
--- src/include/utils/builtins.h 2 Sep 2009 23:11:29 -0000
***************
*** 596,601 ****
--- 596,603 ----
extern Datum pg_get_expr_ext(PG_FUNCTION_ARGS);
extern Datum pg_get_userbyid(PG_FUNCTION_ARGS);
extern Datum pg_get_serial_sequence(PG_FUNCTION_ARGS);
+ extern Datum pg_get_owner_object(PG_FUNCTION_ARGS);
+ extern Datum pg_get_owner_column(PG_FUNCTION_ARGS);
extern Datum pg_get_functiondef(PG_FUNCTION_ARGS);
extern Datum pg_get_function_arguments(PG_FUNCTION_ARGS);
extern Datum pg_get_function_identity_arguments(PG_FUNCTION_ARGS);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
