The list of all the queries

ROW level BEFORE triggers that do not return a row if a check succeeds

Query goal: Find ROW level BEFORE triggers that check a condition based on other rows, raise an exception but do not return the row if the condition check succeeds, i.e., exception is not raised.
Query type: Problem detection (Each row in the result could represent a flaw in the design)
Query reliability: Medium (Medium number of false-positive results)
Query license: MIT License
Fixing suggestion: INSERT and UPDATE triggers should have RETURN NEW in their functions and DELETE triggers should have RETURN OLD in their functions.
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

WITH Tr AS (SELECT
    n.nspname AS trigger_schema,
    t.tgname AS trigger_name,
    em.text AS event_manipulation,        
    c.relname AS trigger_table, 
    np.nspname AS routine_schema,
    p.proname AS routine_name,
     p.prosrc AS routine_definition,
     (SELECT DISTINCT action_condition FROM INFORMATION_SCHEMA.triggers AS it WHERE it.trigger_schema=n.nspname AND it.event_object_table=c.relname AND it.trigger_name=t.tgname) AS action_condition,    
        CASE t.tgtype::integer & 1
            WHEN 1 THEN 'ROW'
            ELSE 'STATEMENT'
        END AS action_orientation,
        CASE t.tgtype::integer & 66
            WHEN 2 THEN 'BEFORE'
            WHEN 64 THEN 'INSTEAD OF'
            ELSE 'AFTER'
        END AS action_timing	
   FROM pg_namespace n,
    pg_class c,
    pg_trigger t,
    pg_proc p,
    pg_namespace np,
    ( VALUES (4,'INSERT'), (8,'DELETE'), (16,'UPDATE'), (32,'TRUNCATE')) em(num, text)
  WHERE n.oid = c.relnamespace 
  AND c.oid = t.tgrelid 
  AND t.tgfoid=p.oid
  AND p.pronamespace=np.oid
  AND (t.tgtype::integer & em.num) <> 0 
  AND NOT t.tgisinternal 
  AND NOT pg_is_other_temp_schema(n.oid))
SELECT trigger_schema, trigger_table, action_timing, event_manipulation, action_orientation, action_condition, routine_name, regexp_replace(routine_definition,'[\r\n]','<br>','g')  AS routine_definition
FROM Tr
WHERE action_orientation='ROW' 
AND action_timing='BEFORE'
AND routine_definition~* 'IF[[:space:]]+.*[[:space:]]THEN'
AND routine_definition~* 'SELECT[[:space:]]+.*[[:space:]]FROM'
AND routine_definition~* 'RAISE[[:space:]]+EXCEPTION' 
AND 
((event_manipulation IN ('INSERT','UPDATE') AND
routine_definition!~* 'RETURN[[:space:]]+NEW') 
OR
(event_manipulation IN ('DELETE') AND
routine_definition!~* 'RETURN[[:space:]]+OLD'))
ORDER BY trigger_schema, trigger_table, routine_name;

Collections where the query belongs to

Collection nameCollection description
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 where the query belongs to

Category nameCategory description
Triggers and rulesQueries of this category provide information about triggers and rules in a database.

Reference materials for further reading

Reference
https://www.postgresql.org/docs/current/plpgsql-trigger.html

The list of all the queries