The list of all the queries

A state machine is implemented with the help of a state classifier table

Query goal: Find implementations of state machines that use a state classifier table.
Notes about the query: The query uses PostgreSQL system catalog tables not information_schema views in order to give precise answer even if foreign key constraints in different tables have the same name. In SQL a foreign key is an ordered set of columns that must contain at least one column. The query result preserves the order of columns in the key. The query considers both table names in English and Estonian.
Query type: General (Overview of some aspect of the database.)
Query license: MIT License
Data source: system catalog only
SQL query: Click on query to copy it

WITH 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,
CASE WHEN o.confupdtype='a' THEN 'NO ACTION'
WHEN o.confupdtype='r' THEN 'RESTRICT'
WHEN o.confupdtype='c' THEN 'CASCADE'
WHEN o.confupdtype='n' THEN 'SET NULL'
WHEN o.confupdtype='d' THEN 'SET DEFAULT' END AS on_update,
CASE WHEN o.confdeltype='a' THEN 'NO ACTION'
WHEN o.confdeltype='r' THEN 'RESTRICT'
WHEN o.confdeltype='c' THEN 'CASCADE'
WHEN o.confdeltype='n' THEN 'SET NULL'
WHEN o.confdeltype='d' THEN 'SET DEFAULT' END AS on_delete
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, on_update, on_delete
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, on_update, on_delete
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,on_update, on_delete)
select conname, foreign_schema, foreign_table, foreign_col, target_schema, target_table, target_col, on_update, on_delete
from fk_with_names
where foreign_table~*'(olek|staatus|seisund|state(?!ment)|status)'
order by target_schema, target_table, conname;

Collections where the query belongs to

Collection nameCollection description
Find problems about base tablesA selection of queries that return information about the data types, field sizes, default values as well as general structure of base tables. Contains all the types of queries - problem detection, software measure, and general overview
Find problems by overviewQueries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .

Categories where the query belongs to

Category nameCategory description
Classifier tablesQueries of this category provide information about registration of classifiers.
Relationships between tablesQueries of this category provide information about how database tables are connected to each other and whether such connections have been explicitly defined and whether it has been done correctly.
Result quality depends on namesQueries of this category use names (for instance, column names) to try to guess the meaning of a database object. Thus, the goodness of names determines the number of false positive and false negative results.
State machineQueries of this category provide information about registration of states of entities in a database.

Reference materials for further reading

Reference
L. Silverston ja P. Agnew, „The Data Model Resource Book, Volume 3: Universal Patterns for Data Modeling,“ Chichester, John Wiley & Sons Ltd, 2009. Level 2 Status Pattern, Current Status
Representing the Status of the Main Entities of an Information System in SQL Databases Master Thesis, in Estonian.

The list of all the queries