Routines that use old syntax for limiting rows

Back to Catalog
Goal This query identifies PL/pgSQL and SQL routines with no SQL-standard bodies that use the non-standard LIMIT clause for row limitation. It flags these routines because the official, cross-platform SQL standard specifies FETCH FIRST n ROWS ONLY for this purpose. Adhering to the standard improves code portability and maintainability. To ensure relevance, the query intelligently excludes routines that are part of installed extensions, focusing only on user-defined code.
Notes To ensure accuracy and relevance, the query incorporates several key considerations. It relies on the pg_proc.prokind column, making it compatible with PostgreSQL 11 and newer. For unique identification of overloaded routines, the output includes not just the schema and routine name, but also its parameters. To improve readability in web-based interfaces, newline characters in the routine's body are replaced with
tags. Crucially, the query intelligently excludes two types of routines:

  • Routines that are part of any installed extension.
  • SQL-language routines that use a SQL-standard body. This exclusion is critical because for these specific routines, the PostgreSQL planner internally rewrites the standard FETCH FIRST n ROWS ONLY clause to a LIMIT n clause, which would otherwise result in false positives.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability Medium (Medium number of false-positive results)
License MIT License
Fixing Suggestion Use FETCH FIRST n ROWS syntax instead of LIMIT.
Data Source INFORMATION_SCHEMA+system catalog
SQL Query 
SELECT 
  n.nspname AS routine_schema, 
  pr.proname AS routine_name, 
   pg_get_function_identity_arguments(pr.oid) AS parameters,
  regexp_replace(pg_get_functiondef(pr.oid),'[\r\n]','
','g')  AS routine_src,  
  CASE WHEN pr.prokind='f' THEN 'FUNCTION'
  WHEN pr.prokind='p' THEN 'PROCEDURE'
  WHEN pr.prokind='w' THEN 'WINDOW FUNCTION' END AS routine_type,
  l.lanname AS routine_language
FROM 
  pg_catalog.pg_proc pr, 
  pg_catalog.pg_namespace n,
  pg_catalog.pg_language l
WHERE 
  pr.pronamespace = n.oid 
  AND pr.prolang=l.oid 
  AND pr.prokind<>'a'  
  AND pg_get_functiondef(pr.oid)~*'(?'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
ORDER BY routine_schema, routine_name, parameters;
Collections

This query belongs to the following collections:

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:

NameDescription
Conformance to SQL standardQueries of this category provide information about the use of constructs that conform to the SQL standard
SyntacticsQueries of this category provide information about syntactic mistakes.
User-defined routinesQueries of this category provide information about the user-defined routines

Further reading and related materials:

Reference
https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-fetch/