Useless trivial trigger functions

Back to Catalog
Goal This query identifies trigger functions that are functionally trivial, specifically those whose sole action is to execute RETURN NEW. In a BEFORE trigger context, this operation simply allows the data modification to proceed unchanged. If the function contains no other logic (e.g., validation, modification of NEW, or side effects), it performs no useful work and incurs unnecessary execution overhead. Such triggers are likely incomplete placeholders or obsolete code that should be removed.
Notes The query does not return functions that only RETURN OLD or RETURN NULL because these could be intentional to prevent data modifications.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability Medium (Medium number of false-positive results)
License MIT License
Data Source INFORMATION_SCHEMA+system catalog
SQL Query 
SELECT 
  pg_namespace.nspname AS routine_schema, 
  pg_proc.proname AS routine_name,  
  regexp_replace( regexp_replace((CASE WHEN pg_proc.prosqlbody IS NULL THEN pg_proc.prosrc ELSE pg_get_function_sqlbody(pg_proc.oid) END),'<','<','g'),'[\r\n]','
','g')  AS routine_body
FROM 
  pg_catalog.pg_proc,
  pg_catalog.pg_namespace,
  pg_catalog.pg_type
WHERE 
  pg_proc.pronamespace = pg_namespace.oid
  AND pg_proc.prorettype = pg_type.oid
  AND typname='trigger'  
 AND pg_namespace.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 ((CASE WHEN pg_proc.prosqlbody IS NULL THEN pg_proc.prosrc ELSE pg_get_function_sqlbody(pg_proc.oid) END)~*'^[[:space:]]*BEGIN[[:space:]]*RETURN[[:space:]]+NEW+;[[:space:]]*END;[[:space:]]*$')
ORDER BY routine_schema, routine_name;
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
StubsQueries of this catergory provide information about stubs (piece of code used to stand in for some other programming functionality).
Triggers and rulesQueries of this category provide information about triggers and rules in a database.