This query audits user-defined routine invocations to enforce named parameter notation, specifically targeting calls to routines with multiple input parameters. It identifies invocations using positional notation (e.g., func(a, b)), which couples the calling logic to the specific order of the callee's signature. Crucially, the query excludes invocations of routines that accept a single input parameter. In single-arity contexts, positional notation is structurally unambiguous and widely accepted. The focus is strictly on multi-parameter calls where positional notation increases the risk of argument misalignment during refactoring.
Type
Problem detection (Each row in the result could represent a flaw in the design)
Refert o parameters by name instead of by their position.
Data Source
INFORMATION_SCHEMA+system catalog
SQL Query
WITH select_routines AS (SELECT
pg_namespace.nspname AS specific_schema,
pg_proc.proname AS routine_name,
pg_proc.proname || '_' || pg_proc.oid AS specific_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(pg_get_functiondef(pg_proc.oid),'[\r\n]',' ','g') AS routine_src,
pg_language.lanname AS routine_language
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 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 r.specific_schema AS user_schema, r.routine_name AS user_name, r.parameters AS user_parametes, r.routine_type AS user_type, r.routine_src AS user_src,
r2.specific_schema AS used_schema, r2.routine_name AS used_name, r2.parameters AS used_parametes, r2.routine_type AS used_type, r2.routine_src AS used_src
FROM select_routines AS r INNER JOIN INFORMATION_SCHEMA.routine_routine_usage AS rru USING (specific_schema, specific_name)
INNER JOIN select_routines AS r2 ON rru.routine_schema=r2.specific_schema AND rru.routine_name=r2.specific_name
WHERE r.routine_src!~*'(=>|:=)'
ORDER BY r.specific_schema, r.routine_name, r.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