Goal Find SQL functions that do not return a value (return VOID) but the SQL statement in the function has RETURNING clause.
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
Fixing Suggestion Specify that either the function returns a value or remove the RETURNING clause.
Data Source INFORMATION_SCHEMA+system catalog
SQL Query
SELECT 
  n.nspname AS func_schema, 
  pr.proname AS func_name, 
   pg_get_function_identity_arguments(pr.oid) AS parameters,
  regexp_replace(pg_get_functiondef(pr.oid),'[\r\n]','
','g') AS func_src FROM pg_catalog.pg_proc pr, pg_catalog.pg_namespace n, pg_catalog.pg_language l, pg_catalog.pg_type t WHERE pr.pronamespace = n.oid AND pr.prolang=l.oid AND pr.prorettype=t.oid AND pr.prokind='f' AND l.lanname='sql' AND t.typname='void' AND pg_get_functiondef(pr.oid)~*'RETURNING[[:space:]]' AND 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) ORDER BY func_schema, func_name, parameters;
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
InconsistenciesQueries of this catergory provide information about inconsistencies of solving the same problem in different places.
User-defined routinesQueries of this category provide information about the user-defined routines

Further reading and related materials:

Reference
https://www.postgresql.org/docs/current/dml-returning.html