Folks,
I've been trying to extract some information about referencing tables
from information_schema, and discovering that it isn't there.
For example, take the following FK, from table Errors to table Files:
CONSTRAINT errors_file_fkey FOREIGN KEY (file)
REFERENCES files (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
What's listed in referential_constraints is:
constraint_catalog | constraint_schema | constraint_name |
unique_constraint_catalog | unique_constraint_schema |
unique_constraint_name | match_option | update_rule | delete_rule
apple3 | public | errors_file_fkey | apple3
| public | files_pkey |
NONE | CASCADE | CASCADE
Ok, there's some useful information about the *referenced* table,
including its unique constraint. Doesn't tell us anything about the
*referencing* table, though.
Let's look up the data on errors_file_fkey constraint in
constrain_column_usage:
table_catalog | table_schema | table_name | column_name |
constraint_catalog | constraint_schema | constraint_name
apple3 | public | files | id | apple3
| public | errors_file_fkey
Huh? This shows errors_file_fkey constraint defined on the *referenced*
table only.
constraint_table_usage has this:
table_catalog | table_schema | table_name | constraint_catalog |
constraint_schema | constraint_name
---------------+--------------+------------+--------------------+-------------------+------------------
apple3 | public | files | apple3 |
public | errors_file_fkey
By information_schema, you wouldn't have any idea that errors_file_fkey
is defined on the table Errors, let alone what columns it's defined
against.
Poke around; you'll discover that there is no information about
referencing tables in information_schema at all.
This has got to be a bug, even if the SQL standard can be read to
support it.
I'm happy to write some code to fix it, if we can agree what these views
should show. I think constraint_column_usage and constraint_table_usage
should be showing the data of both the referenced and referencing tables.
--Josh Berkus
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
