Re: pg_get_triggerdef can't find the trigger using OID.

[bricklen <bricklen@xxxxxxxxx>]

On Fri, Aug 16, 2013 at 8:01 AM, AI Rumman <rummandba@xxxxxxxxx> wrote:

Why can't pg_get_triggerdef find the trigger using OID.

testdb=# SELECT 

testdb-#   p.oid,

testdb-#   n.nspname as "Schema",

testdb-#   p.proname as "Name",

testdb-#   pg_catalog.pg_get_function_result(p.oid) as "Result data type",

testdb-#   pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",

testdb-#  CASE

testdb-#   WHEN p.proisagg THEN 'agg'

testdb-#   WHEN p.proiswindow THEN 'window'

testdb-#   WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'

testdb-#   ELSE 'normal'

testdb-# END as "Type"

testdb-# FROM pg_catalog.pg_proc p

testdb-#      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace

testdb-# WHERE pg_catalog.pg_function_is_visible(p.oid)

testdb-#       AND n.nspname <> 'pg_catalog'

testdb-#       AND n.nspname <> 'information_schema'

testdb-# ORDER BY 1, 2, 4;

  oid  | Schema |  Name   | Result data type |                     Argument data types                      |  Type   

-------+--------+---------+------------------+--------------------------------------------------------------+---------

 18249 | public | test_f  | trigger          |                                                              | trigger

testdb=# select pg_get_triggerdef(18249);

ERROR:  could not find tuple for trigger 18249

Thanks.

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