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.
SQL Query to Generate Fix
Description
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:
Name
Description
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 .
Categories
This query is classified under the following categories:
Name
Description
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.