Packages
Ecto PostgreSQL database performance insights. Locks, index usage, buffer cache hit ratios, vacuum stats and more.
Current section
Files
Jump to
Current section
Files
lib/queries/table_foreign_keys.ex
defmodule EctoPSQLExtras.TableForeignKeys do
@behaviour EctoPSQLExtras
def info do
%{
title: "Foreign keys info",
columns: [
%{name: :table_name, type: :string},
%{name: :constraint_name, type: :string},
%{name: :column_name, type: :string},
%{name: :foreign_table_name, type: :string},
%{name: :foreign_column_name, type: :string}
]
}
end
def query(args \\ []) do
if args[:table_name] do
"""
/* ECTO_PSQL_EXTRAS: Foreign keys info for a specific table */
SELECT
conrelid::regclass::text AS table_name,
conname AS constraint_name,
a.attname AS column_name,
confrelid::regclass::text AS foreign_table_name,
af.attname AS foreign_column_name
FROM
pg_constraint AS c
JOIN
pg_attribute AS a ON a.attnum = ANY(c.conkey) AND a.attrelid = c.conrelid
JOIN
pg_attribute AS af ON af.attnum = ANY(c.confkey) AND af.attrelid = c.confrelid
WHERE
c.contype = 'f'
AND c.conrelid = '<%= table_name %>'::regclass;
"""
|> EEx.eval_string(args)
else
"""
/* ECTO_PSQL_EXTRAS: Foreign keys info for all tables */
SELECT
conrelid::regclass::text AS table_name,
conname AS constraint_name,
a.attname AS column_name,
confrelid::regclass::text AS foreign_table_name,
af.attname AS foreign_column_name
FROM
pg_constraint AS c
JOIN
pg_attribute AS a ON a.attnum = ANY(c.conkey) AND a.attrelid = c.conrelid
JOIN
pg_attribute AS af ON af.attnum = ANY(c.confkey) AND af.attrelid = c.confrelid
WHERE
c.contype = 'f';
"""
end
end
end