Avoid using length function

Back to Catalog
Goal This query identifies all expressions that use the non-standard length() function. Although length() is a functional synonym for char_length() in PostgreSQL, its use is discouraged for two primary reasons: char_length() is the SQL-standard function, and length() has different semantics in other database systems (e.g., returning byte length in MySQL). To enhance code portability and prevent semantic ambiguity for developers, this query flags all instances of length() to encourage standardization on the char_length() function.
Notes To ensure its results are accurate and readable, the query incorporates several key features. For unique identification of overloaded routines, the output includes not just the schema and routine name, but also its full parameter signature. The query focuses exclusively on user-defined code by excluding any routines that are part of an installed extension. Finally, to improve readability in web browsers, newline characters within the bodies of routines and the definitions of views/materialized views are replaced with HTML
tags.
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 char_length function instead of length function.
Data Source INFORMATION_SCHEMA+system catalog
SQL Query 
WITH expressions AS (select 
n.nspname as schema,
c.relname || '.' || o.conname as name, 
substring(pg_get_constraintdef(o.oid),7) as expression,
'TABLE CHECK' AS type
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_namespace n on n.oid=c.relnamespace
where o.contype ='c'   
and n.nspname not in (select schema_name
from information_schema.schemata
where schema_name<>'public' and
schema_owner='postgres' and schema_name is not null)
union select n.nspname as schema,
t.typname || '.' || o.conname as name, 
substring(pg_get_constraintdef(o.oid),7) as expression,
'DOMAIN CHECK' AS type
from pg_constraint o inner join pg_type t on t.oid = o.contypid
inner join pg_namespace n on n.oid=t.typnamespace
where o.contype ='c' and n.nspname not in (select schema_name
from information_schema.schemata
where schema_name<>'public' and
schema_owner='postgres' and schema_name is not null)
UNION SELECT trigger_schema, event_object_table || '.' || trigger_name, action_condition, 'TRIGGER WHEN' AS type
FROM INFORMATION_SCHEMA.triggers
WHERE action_condition IS NOT NULL
UNION SELECT schemaname, tablename || '.' || rulename, definition, 'RULE WHERE' AS type
FROM pg_catalog.pg_rules
WHERE schemaname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
UNION SELECT table_schema, table_name, view_definition, type
FROM (SELECT  table_schema, table_name, regexp_replace(view_definition,'[\r\n]','
','g') AS view_definition, 'VIEW SUBQUERY' AS type
FROM information_schema.views
WHERE table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
UNION SELECT schemaname, matviewname, regexp_replace(definition,'[\r\n]','
','g') AS definition, 'MATERIALIZED VIEW SUBQUERY' AS type
FROM pg_catalog.pg_matviews) AS foo
WHERE view_definition ~*'^.+([[:space:]]+where[[:space:]]+|[[:space:]]+having[[:space:]]+).+$'
UNION SELECT pg_namespace.nspname AS routine_schema,  pg_proc.proname || '(' || pg_get_function_identity_arguments(pg_proc.oid)  || ')' AS routine_name,  regexp_replace(pg_proc.prosrc,'[\r\n]','
','g')  AS routine_src, 'ROUTINE BODY' AS type
FROM pg_catalog.pg_proc, pg_catalog.pg_namespace
WHERE pg_proc.pronamespace = pg_namespace.oid 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 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 schema, name AS object_identifier, type AS object_type, expression AS suspected_expression
FROM expressions
WHERE expression ~*'(?
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
CHECK constraintsQueries of this category provide information about CHECK constraints.
Conformance to SQL standardQueries of this category provide information about the use of constructs that conform to the SQL standard
Derived tablesQueries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.
Triggers and rulesQueries of this category provide information about triggers and rules in a database.
User-defined routinesQueries of this category provide information about the user-defined routines