Goal This query identifies PL/pgSQL routines that invoke RAISE EXCEPTION and have malformed, generic, or improperly classified ERRCODE values in RAISE EXCEPTION statements. It acts as a linter for PostgreSQL error handling best practices. Specifically, it flags generic codes (like P0001 or raise_exception), the misuse of warning/info classes (00-02) as breaking exceptions, and the hijacking of reserved standard error classes. It also ensures that custom error codes follow the correct 5-character format and start with a digit between 5 and 9, thereby preventing conflicts with built-in PostgreSQL errors.
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,
  substring(pg_proc.prosrc from '(?i)\yERRCODE\s*=\s*''([^'']+)''') AS errcode_val,
  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,
errcode_val AS problematic_errcode,
    CASE 
        WHEN errcode_val IN ('P0001', 'raise_exception') THEN 
            'Rule 4: Too generic error (P0001 / raise_exception). A specific custom code should be created.'
        WHEN errcode_val ~ '^(00|01|02)' THEN 
            'Rule 5: Using a warning or info class (00-02) as a breaking exception.'
        WHEN errcode_val ~ '^[0-4A-Z][A-Z0-9]{4}$' THEN 
            'Rule 2 & 3: A numeric code is used for a standard error (use a text alias) or a standard class is hijacked.'           
        WHEN errcode_val !~ '^[a-z_]+$' AND errcode_val !~ '^[5-9][A-Z0-9]{4}$' THEN 
            'Rule 1: Invalid format! Must be a lowercase text alias or a 5-character code starting with digits 5-9.'
        ELSE 'Unspecified linting error'
    END AS reason,
routine_src_display
FROM pl_pgsql_routine
WHERE     
    errcode_val IN ('P0001', 'raise_exception')    
    OR (
        errcode_val !~ '^[a-z_]+$' 
        AND errcode_val !~ '^[5-9][A-Z0-9]{4}$'
    )
ORDER BY routine_schema, routine_name, parameters;

Fixing Suggestion

To resolve this issue, review the flagged ERRCODE and update it according to PostgreSQL standards:

  1. Avoid the generic P0001; create a specific error code for your application logic.

  2. If throwing a built-in PostgreSQL error, use its lowercase text alias (e.g., unique_violation) for better readability instead of its alphanumeric code.

  3. For custom business logic exceptions, define a dedicated 5-character SQLSTATE code starting with a digit between 5 and 9 (e.g., 50001) to avoid hijacking reserved system classes.

  4. Ensure the error class matches the severity (do not use 00, 01, or 02 for breaking exceptions).

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