CREATE OR REPLACE VIEW my_foreign_keys AS
SELECT s1.constraint_name, s1.table_name, s1.column_name, s1.ordinal_position, s2.table_name_ref, s2.column_name_ref, s2.ordinal_position_ref
FROM ( SELECT key_column_usage.constraint_name, key_column_usage.table_name, key_column_usage.column_name, columns.ordinal_position
FROM information_schema.key_column_usage
JOIN information_schema.columns USING (table_name, column_name)) s1
JOIN ( SELECT constraint_column_usage.constraint_name, constraint_column_usage.table_name AS table_name_ref, constraint_column_usage.column_name AS column_name_ref, cols_ref.ordinal_position AS ordinal_position_ref
FROM information_schema.constraint_column_usage
JOIN information_schema.columns cols_ref ON cols_ref.table_name::text = constraint_column_usage.table_name::text AND cols_ref.column_name::text = constraint_column_usage.column_name::text) s2 ON s1.constraint_name::text = s2.constraint_name::text AND NOT s1.table_name::text = s2.table_name_ref::text;
CREATE OR REPLACE VIEW my_foreign_keys AS
SELECT s1.constraint_name, s1.table_name, s1.column_name, s1.ordinal_position, s2.table_name_ref, s2.column_name_ref, s2.ordinal_position_ref
FROM ( SELECT key_column_usage.constraint_name, key_column_usage.table_name, key_column_usage.column_name, columns.ordinal_position
FROM information_schema.key_column_usage
JOIN information_schema.columns USING (table_name, column_name)) s1
JOIN ( SELECT constraint_column_usage.constraint_name, constraint_column_usage.table_name AS table_name_ref, constraint_column_usage.column_name AS column_name_ref, cols_ref.ordinal_position AS ordinal_position_ref
FROM information_schema.constraint_column_usage
JOIN information_schema.columns cols_ref ON cols_ref.table_name::text = constraint_column_usage.table_name::text AND cols_ref.column_name::text = constraint_column_usage.column_name::text) s2 ON s1.constraint_name::text = s2.constraint_name::text AND NOT s1.table_name::text = s2.table_name_ref::text;
To copy to clipboard, switch view to plain text mode
Bookmarks