This query identifies SQL routines that exhibit a dangerous combination of state-changing side effects (DML) and a static return value (either a constant literal or an unmodified input parameter). The function's name and signature often imply that the return value is the result of its operations (e.g., a new balance, a generated ID). However, the static return value contradicts this, creating a semantic disconnect between the routine's name and its contract. This is a significant design flaw that can lead to subtle but critical bugs, as the calling code may act on a return value that does not accurately reflect the database state after the routine's execution.
Type
Problem detection (Each row in the result could represent a flaw in the design)
Use instead RETURNING clause in a data modification statement.
Data Source
INFORMATION_SCHEMA+system catalog
SQL Query
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='w' THEN 'WINDOW FUNCTION' END AS routine_type,
regexp_replace( regexp_replace(pg_get_functiondef(pg_proc.oid),'<','<','g'),'[\r\n]',' ','g') AS routine_src
FROM
pg_catalog.pg_proc,
pg_catalog.pg_namespace,
pg_catalog.pg_language
WHERE
pg_proc.pronamespace = pg_namespace.oid
AND pg_proc.prolang = pg_language.oid
AND pg_proc.prokind<>'a'
AND lanname='sql'
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 pg_get_functiondef(pg_proc.oid)~*'(?<=;[[:space:]]*)(RETURN|SELECT)[[:space:]]+[[:alnum:]_]+([[:space:]]+AS[[:space:]]+[[:alnum:]_]+){0,1}[[:space:]]*;'
ORDER BY routine_schema, routine_name, parameters;
Collections
This query belongs to the following collections:
Name
Description
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 .
Categories
This query is classified under the following categories:
Name
Description
User-defined routines
Queries of this category provide information about the user-defined routines