On Fri, Aug 16, 2013 at 8:01 AM, AI Rumman <rumman...@gmail.com> 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
