Find foreign key constraints in case of which the name of one of the tables is in plural and the name of another table is in singular.
Notes
The query works somewhat correctly only if the names are in Estoniand. The query is based on a simplified assumption that in case of many words in Estonian (of course, not all) the plural form is indicated by the letter "d" at the end of the word. If the name or its subcomponent ends with de_ or te_, then it is considered plural. Names that for example end with id, oid, kood, or uuid are considered singular. Thus, "auto" is considered singular whereas "autod" and "autode_aruanne" are considered plural.
Type
Problem detection (Each row in the result could represent a flaw in the design)
WITH plural_singular AS (SELECT nspname AS table_schema, relname AS table_name,
CASE WHEN relname ~* '(d$|.+(d|[^t]t)e_.+)' AND relname !~* '((^|_)id$|kood$|(^|_)oid$|(^|_)uuid$|hind$|seisund$|brand$|kavand$|kavand$|^d*d$|periood$|pind$|sisend$|laud$|kraad$|osakond$)' THEN 'Perhaps plural' ELSE 'Perhaps singular' END AS comment_about_the_table_name
FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid
WHERE relkind IN ('r','v','m','f','p')
AND nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)),
fk as (select
o.conname,
(select nspname from pg_namespace where oid=f.relnamespace) as foreign_schema,
f.relname as foreign_table,
f.oid as foreign_table_oid,
o.confkey AS foreign_col,
(select nspname from pg_namespace where oid=c.relnamespace) as target_schema,
c.relname as target_table,
c.oid as target_table_oid,
o.conkey AS target_col
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_class f on f.oid = o.confrelid
where o.contype = 'f'),
fk_unnest as (select conname, foreign_schema, foreign_table, foreign_table_oid, foreign_col, foreign_col_num, target_schema, target_table, target_table_oid, target_col, target_col_num, ordin
from fk, unnest(fk.foreign_col, fk. target_col) with ordinality as f(foreign_col_num, target_col_num, ordin)),
fk_with_names as (select conname, foreign_schema, foreign_table, array_agg(a_foreign.attname order by ordin) as foreign_col, target_schema, target_table, array_agg(a_target.attname order by ordin) as target_col
from fk_unnest fk inner join pg_attribute a_foreign on fk.foreign_col_num = a_foreign.attnum and fk.foreign_table_oid = a_foreign.attrelid and a_foreign.attisdropped = false
inner join pg_attribute a_target on fk.target_col_num = a_target.attnum and fk.target_table_oid = a_target.attrelid and a_target.attisdropped = false
group by conname, foreign_schema, foreign_table, target_schema, target_table)
select conname, foreign_schema, foreign_table, foreign_col, psf.comment_about_the_table_name AS foreign_comment,
target_schema, target_table, target_col, pst.comment_about_the_table_name AS target_comment
from fk_with_names AS fwn INNER JOIN plural_singular AS psf ON fwn.foreign_schema=psf.table_schema AND fwn.foreign_table=psf.table_name
INNER JOIN plural_singular AS pst ON fwn.target_schema=pst.table_schema AND fwn.target_table=pst.table_name
WHERE psf.comment_about_the_table_name<>pst.comment_about_the_table_name
order by target_schema, target_table, conname;
Categories
This query is classified under the following categories:
Name
Description
Inconsistencies
Queries of this catergory provide information about inconsistencies of solving the same problem in different places.
Naming
Queries of this category provide information about the style of naming.