Goal Find table functions that use OFFSET. OFFSET method is a common way for implementing pagination.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability Low (Many false-positive results)
License MIT License
Fixing Suggestion Consider using the seek method instead.
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,
  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, pg_catalog.pg_type WHERE pg_proc.pronamespace = pg_namespace.oid AND pg_proc.prolang = pg_language.oid AND pg_type.oid=pg_proc.prorettype AND pg_proc.prokind='f' AND pg_proc.proretset=true AND pg_get_functiondef(pg_proc.oid) ~'OFFSET[[:space:]]' 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 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) 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) ORDER BY routine_schema, routine_name, parameters;
Categories

This query is classified under the following categories:

NameDescription
PerformanceQueries of this category provide information about indexes in a database.
User-defined routinesQueries of this category provide information about the user-defined routines