Insufficient number of user-defined non-trigger SQL routines with SQL-standard routine body

Back to Catalog
Goal This query assesses the database's adherence to modern SQL standards regarding procedural code. It verifies whether the schema contains a minimum of 3 user-defined, non-trigger routines that are specifically written in the SQL language and utilize a SQL-standard body (defined using BEGIN ATOMIC ... END). This excludes routines written in PL/pgSQL or those using the legacy string-literal body definition. The metric ensures a baseline adoption of the standardized, portable syntax for SQL routines introduced in newer PostgreSQL versions.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability High (Few or no false-positive results)
License MIT License
Data Source INFORMATION_SCHEMA+system catalog
SQL Query 
WITH routines AS (SELECT 
  routines.routine_schema, 
  routines.routine_name,
  pg_get_functiondef(translate(substring(routines.specific_name,'_[0-9]+$'),'_','')::int::oid) AS routine_src
FROM 
  information_schema.routines
WHERE routine_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL) AND (data_type<>'trigger' OR data_type IS NULL)
AND NOT EXISTS (SELECT 1
FROM pg_catalog.pg_depend d inner join pg_catalog.pg_proc pc ON d.objid=pc.oid
WHERE EXISTS (SELECT 1 FROM pg_catalog.pg_extension e WHERE d.refobjid=e.oid) AND
pc.proname || '_' || pc.oid = routines.specific_name) 
 AND external_language = 'SQL'
 AND routine_name NOT IN ('f_assume_you_must_use_files', 'f_check_format_comma_separated_list', 'f_check_password', 'f_default_value_with_no_match')) 
SELECT 'Too few user-defined routines, must be at least four' As comment, (SELECT Count(*) AS cnt FROM Routines WHERE routine_src~*'BEGIN[[:space:]]+ATOMIC') AS number_of_sql_routines
WHERE (SELECT Count(*) AS cnt FROM routines
WHERE routine_src~*'BEGIN[[:space:]]+ATOMIC')<3;
Collections

This query belongs to the following collections:

NameDescription
Find problems automaticallyQueries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .
Categories

This query is classified under the following categories:

NameDescription
AssessmentQueries of this category could be used specifically in the learning environment to assess as to whether student projects have filled certain criteria.
User-defined routinesQueries of this category provide information about the user-defined routines