The list of all the queries

SQL routines that return the value of an input parameter

Query goal: Find SQL routines that return the value of an input parameter.
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: Use instead RETURNING clause in a data modification statement.
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

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),'<','&lt','g'),'[\r\n]','<br>','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[[:space:]]+[[:alnum:]_]+;'
ORDER BY routine_schema, routine_name, parameters;

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
User-defined routinesQueries of this category provide information about the user-defined routines

The list of all the queries