Re: pg_get_triggerdef can't find the trigger using OID. - Mailing list pgsql-general

Is it because you need the oid from pg_trigger, rather than pg_proc?

The following query is a fragment of one I needed to put together the other day and it might be useful to you (the last few SELECT columns are taken from your query)

SELECT  DISTINCT
        tr.oid,
        n.nspname as schemaname,
        c.relname as tablename,
        tr.tgname as triggername,
        pr.proname as function_name,
        pg_catalog.pg_get_function_result(pr.oid) as "Result data type",
        pg_catalog.pg_get_function_arguments(pr.oid) as "Argument data types",
        CASE WHEN pr.proisagg THEN 'agg' WHEN pr.proiswindow THEN 'window' WHEN pr.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'   ELSE 'normal' END as "Type",
        CASE WHEN pr.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN pg_get_triggerdef(tr.oid) ELSE NULL END as trigger_def
FROM pg_catalog.pg_class as c
INNER JOIN pg_catalog.pg_attribute as a ON (a.attrelid = c.oid)
INNER JOIN pg_catalog.pg_type as t ON (t.oid = a.atttypid)
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_tablespace ts ON ts.oid = c.reltablespace
LEFT JOIN pg_trigger tr ON tr.tgrelid::regclass::text = c.relname
LEFT JOIN pg_proc pr ON pr.oid = tr.tgfoid
WHERE a.attnum > 0      -- no system cols
AND NOT attisdropped    -- no dropped cols
AND c.relkind = 'r'
AND tr.tgisinternal is not true
AND tr.tgname IS NOT NULL
ORDER BY n.nspname, c.relname