Find as to whether both functions length and char_length are used in the database.
Notes
In the returned body of routine and subquery of view/materialized view the query replaces each newline character with the line break (br) tag for the better readability in case the query result is displayed in a web browser. The query does not consider the routines that are a part of an extension. There could be multiple routines with the same name but with different parameters in the same schema (overloading). Thus, for the unique identification of the routine it is necessary to present also its parameters in addition to the schema name and routine name.
Type
Problem detection (Each row in the result could represent a flaw in the design)
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)),
objects_length AS (SELECT schema, name AS object_identifier, type AS object_type, expression AS suspected_expression
FROM expressions
WHERE expression ~*'(?
Categories
This query is classified under the following categories:
Name
Description
CHECK constraints
Queries of this category provide information about CHECK constraints.
Derived tables
Queries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.
Inconsistencies
Queries of this catergory provide information about inconsistencies of solving the same problem in different places.
Triggers and rules
Queries of this category provide information about triggers and rules in a database.
User-defined routines
Queries of this category provide information about the user-defined routines