Goal This query identifies PL/pgSQL routines that invoke RAISE EXCEPTION and embed dynamic values directly into the primary error message string. This is considered an anti-pattern because it generates a unique error string for every failure, which complicates log aggregation and client-side error parsing. The primary error message should always be static and predictable.
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)
Data Source INFORMATION_SCHEMA+system catalog
SQL Query
WITH pl_pgsql_routine AS (SELECT 
  pg_namespace.nspname AS routine_schema, 
  pg_proc.proname AS routine_name, 
  pg_get_function_identity_arguments(pg_proc.oid) AS parameters,
  CASE WHEN pg_proc.prokind='f' THEN 'FUNCTION'
  WHEN pg_proc.prokind='p' THEN 'PROCEDURE'
  WHEN pg_proc.prokind='a' THEN 'AGGREGATE FUNCTION'
  WHEN pg_proc.prokind='w' THEN 'WINDOW FUNCTION' END AS routine_type,
  pg_type.typname AS return_type,
  pg_proc.prosrc AS routine_src,
  regexp_replace(pg_proc.prosrc,'[\r\n]','<br>','g')  AS routine_src_display
FROM 
  pg_catalog.pg_proc, 
  pg_catalog.pg_namespace,
  pg_catalog.pg_language,
  pg_catalog.pg_type
WHERE 
  pg_proc.pronamespace = pg_namespace.oid AND
  pg_proc.prolang = pg_language.oid AND
  pg_proc.prorettype = pg_type.oid AND
    pg_language.lanname='plpgsql' AND
   pg_proc.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 
  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 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=pg_proc.oid))
SELECT routine_schema, 
routine_name,
parameters,
routine_type,
return_type,
routine_src_display
FROM pl_pgsql_routine
WHERE routine_src ~* 'RAISE[[:space:]]+EXCEPTION[[:space:]]*''[^'']*%'
ORDER BY routine_schema, routine_name, parameters;

Fixing Suggestion

To resolve this issue, refactor the RAISE EXCEPTION statement. Keep the primary error message as a static, predictable string, and move all dynamic variables (such as the specific problematic data) into the DETAIL clause. For example, instead of RAISE EXCEPTION 'Invalid code %', NEW.course_code; in a trigger routine, use RAISE EXCEPTION 'Invalid code provided' USING DETAIL = format('Problematic code: %s', NEW.course_code);

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:

Triggers and rules

Queries of this category provide information about triggers and rules in a database.

User-defined routines

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

NameDescription
Triggers and rulesQueries of this category provide information about triggers and rules in a database.
User-defined routinesQueries of this category provide information about the user-defined routines