Goal Find user-defined routines that implement database operations (comment refers to an operation) but show these only if there are at least eight such routines. Contracts of database operations are specified in the system analysis documentation. The contracts apply the idea of design by contract in the field of databases.
Notes The query assumes that the comment of each routine that implements a database operation contains the short identifier of the database operation, which starts with the letters OP. In the returned body of routine the query replaces each newline character with the line break (br) tag for the better readability in case the query result is displayed in a web browser. There could be multiple routines with the same name but with different parameters in the same schema (overloading). Thus, for the unique identification of the routine it is necessary to present also its parameters in addition to the schema name and routine name.
Type General (Overview of some aspect of the database.)
License MIT (opens in new tab)
Data Source INFORMATION_SCHEMA+system catalog
SQL Query 
WITH comments_on_routines AS (
SELECT 
  n.nspname AS routine_schema, 
  p.proname AS routine_name,
  pg_get_function_identity_arguments(p.oid) AS parameters,
  d.description AS comment
FROM pg_catalog.pg_proc p INNER JOIN pg_catalog.pg_namespace n ON p.pronamespace = n.oid
LEFT JOIN pg_catalog.pg_description d ON p.oid=d.objoid
WHERE n.nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL) 
AND p.proname NOT IN ('f_assume_you_must_use_files', 'f_check_format_comma_separated_list', 'f_check_password', 'f_default_value_with_no_match') 
AND NOT EXISTS (SELECT 1
FROM pg_catalog.pg_depend d 
WHERE EXISTS (SELECT 1 FROM pg_catalog.pg_extension e WHERE d.refobjid=e.oid) AND
d.objid=p.oid)
AND d.description LIKE '%OP%'),
user_defined_routines AS (
SELECT 
  routines.routine_schema, 
  routines.routine_name, 
  pg_get_function_identity_arguments(translate(substring(specific_name,'_[0-9]+$'),'_','')::int::oid) AS parameters,
  routines.data_type AS return_type, 
  routines.is_deterministic, 
  routines.routine_type,
  routines.security_type,
  routines.external_language,
 regexp_replace(pg_get_functiondef(translate(substring(routines.specific_name,'_[0-9]+$'),'_','')::int::oid),'[\r\n]','<br>','g')  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 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')
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))
SELECT routine_schema, routine_name,  parameters, return_type, is_deterministic,  routine_type, security_type, external_language, routine_src
FROM user_defined_routines INNER JOIN comments_on_routines USING (routine_schema, routine_name, parameters)
WHERE (SELECT Count(*)>=8 FROM comments_on_routines AS cr WHERE comment LIKE '%OP%')
ORDER BY routine_schema, routine_name, parameters;

Categories

This query is classified under the following categories:

Assessment

Queries of this category could be used specifically in the learning environment to assess as to whether student projects have filled certain criteria.

User-defined routines

Queries of this category provide information about the user-defined routines

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