From: Alexander Gataric [mailto:[email protected]]
Sent: Thursday, September 13, 2012 12:52 PM
To: [email protected]
Subject: Best free tool for relationship extraction
I need to determine relationships between several tables. Is there a free tool
to extract these from catalog tables? Is there an SQL that also does this?
Thanks
Alex
Try this SQL:
WITH RECURSIVE FK_recursive(distance, child_table, parent_table,
FK_constraint_name, unique_constraint_name,
ON_DELETE, ON_UPDATE, is_deferrable,
FK_path) AS (
SELECT 1, tc.table_name, ctu.table_name, ctu.constraint_name,
rc.unique_constraint_name,
rc.delete_rule, rc.update_rule, tc.is_deferrable,
quote_ident(ctu.table_name)
FROM information_schema.constraint_table_usage ctu,
information_schema.table_constraints tc,
information_schema.referential_constraints rc
WHERE ctu.table_name = 'gp_part_space' and ctu.table_catalog = 'vector'
and tc.constraint_name = ctu.constraint_name and tc.constraint_type =
'FOREIGN KEY' and tc.constraint_catalog = 'vector'
and ctu.constraint_name = rc.constraint_name
UNION ALL
SELECT er.distance + 1, tc.table_name, ctu.table_name, ctu.constraint_name,
rc.unique_constraint_name,
rc.delete_rule, rc.update_rule, tc.is_deferrable, er.FK_path || ' <-
' || quote_ident(ctu.table_name)
FROM FK_recursive er, information_schema.constraint_table_usage ctu,
information_schema.table_constraints tc,
information_schema.referential_constraints rc
WHERE er.child_table = ctu.table_name and ctu.table_catalog = 'vector'
and tc.constraint_name = ctu.constraint_name and tc.constraint_type =
'FOREIGN KEY' and tc.constraint_catalog = 'vector'
and ctu.constraint_name = rc.constraint_name
)
SELECT distance, child_table, parent_table, FK_constraint_name,
unique_constraint_name,
ON_DELETE, ON_UPDATE, is_deferrable, FK_path || ' <- ' ||
quote_ident(child_table) AS FK_path
FROM FK_recursive ORDER BY distance, parent_table;
If you get an error like this (possible on 8.4.5):
ERROR: operator is not unique: smallint[] <@ smallint[]
LINE 1: select $1 <@ $2 and $2 <@ $1
^
HINT: Could not choose a best candidate operator. You might need to add
explicit type casts.
QUERY: select $1 <@ $2 and $2 <@ $1
CONTEXT: SQL function "_pg_keysequal" during inlining
Then recompile the function:
SET search_path TO information_schema;
CREATE OR REPLACE FUNCTION _pg_keysequal(smallint[], smallint[]) RETURNS boolean
LANGUAGE sql IMMUTABLE
AS 'select $1 operator(pg_catalog.<@) $2 and $2 operator(pg_catalog.<@) $1';
SET search_path TO public;
And then re-run original recursive query.
Regards,
Igor Neyman
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
