Goal Do not keep in your database elements that are not needed by anybody. These should be put in use or dropped, otherwise these are dead code.
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 (opens in new tab)
Fixing Suggestion Associate the function with a trigger or drop it.
Data Source INFORMATION_SCHEMA+system catalog
SQL Query 
WITH trigger_routines AS (SELECT 
  routines.routine_schema, 
  routines.routine_name
FROM 
  information_schema.routines
WHERE routine_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL) AND data_type='trigger'),
routines_with_triggers AS (SELECT n.nspname AS routine_schema, p.proname AS routine_name
FROM pg_catalog.pg_trigger t INNER JOIN pg_catalog.pg_proc p ON t.tgfoid=p.oid INNER JOIN
pg_catalog.pg_namespace n ON p.pronamespace=n.oid
)
SELECT routine_schema, routine_name
FROM trigger_routines tr
WHERE NOT EXISTS (SELECT 1 
FROM routines_with_triggers rwt
WHERE tr.routine_schema=rwt.routine_schema AND tr.routine_name=rwt.routine_name)
ORDER BY routine_schema, routine_name;

SQL statements that help generate fixes for the identified problem.

Fix Action #1
WITH trigger_routines AS (SELECT 
  routines.routine_schema, 
  routines.routine_name
FROM 
  information_schema.routines
WHERE routine_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL) AND data_type='trigger'),
routines_with_triggers AS (SELECT n.nspname AS routine_schema, p.proname AS routine_name
FROM pg_catalog.pg_trigger t INNER JOIN pg_catalog.pg_proc p ON t.tgfoid=p.oid INNER JOIN
pg_catalog.pg_namespace n ON p.pronamespace=n.oid
)
SELECT format('DROP FUNCTION %1$I.%2$I RESTRICT;', routine_schema, routine_name) AS statements
FROM trigger_routines tr
WHERE NOT EXISTS (SELECT 1 
FROM routines_with_triggers rwt
WHERE tr.routine_schema=rwt.routine_schema AND tr.routine_name=rwt.routine_name)
ORDER BY routine_schema, routine_name;

Drop the function. The query assumes that trigger functions do not have parameters.

SQL Query to Generate FixDescription
WITH trigger_routines AS (SELECT 
  routines.routine_schema, 
  routines.routine_name
FROM 
  information_schema.routines
WHERE routine_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL) AND data_type='trigger'),
routines_with_triggers AS (SELECT n.nspname AS routine_schema, p.proname AS routine_name
FROM pg_catalog.pg_trigger t INNER JOIN pg_catalog.pg_proc p ON t.tgfoid=p.oid INNER JOIN
pg_catalog.pg_namespace n ON p.pronamespace=n.oid
)
SELECT format('DROP FUNCTION %1$I.%2$I RESTRICT;', routine_schema, routine_name) AS statements
FROM trigger_routines tr
WHERE NOT EXISTS (SELECT 1 
FROM routines_with_triggers rwt
WHERE tr.routine_schema=rwt.routine_schema AND tr.routine_name=rwt.routine_name)
ORDER BY routine_schema, routine_name;
Drop the function. The query assumes that trigger functions do not have parameters.

Collections

This query belongs to the following collections:

Find problems automatically

Queries, 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 .

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:

Comfortability of database evolution

Queries of this category provide information about the means that influence database evolution.

Triggers and rules

Queries of this category provide information about triggers and rules in a database.

Unused implementation elements

Queries of this catergory provide information about the database objects that are not used.

NameDescription
Comfortability of database evolutionQueries of this category provide information about the means that influence database evolution.
Triggers and rulesQueries of this category provide information about triggers and rules in a database.
Unused implementation elementsQueries of this catergory provide information about the database objects that are not used.

Further reading and related materials:

https://en.wikipedia.org/wiki/Dead_code
The corresponding code smells in case of cleaning code are "F4: Dead Function" and "G9: Dead Code". (Robert C. Martin, Clean Code)
Reference
https://en.wikipedia.org/wiki/Dead_code
The corresponding code smells in case of cleaning code are "F4: Dead Function" and "G9: Dead Code". (Robert C. Martin, Clean Code)