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 (opens in new tab)
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]','<br>','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:

Find problems automatically

Queries, 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 .

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:

Inconsistencies

Queries of this catergory provide information about inconsistencies of solving the same problem in different places.

User-defined routines

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

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