usage of pg_get_functiondef() -- SQL state 42809
-- This works.
select
TRG.tgname, TFX.proname, pg_get_functiondef(TFX.oid) as fdef
from
pg_trigger TRG
inner join pg_proc TFX on TFX.oid = TRG.tgfoid
where
TRG.tgisinternal = true
-- This blows up. -- SQL state: 42809 -- ERROR: "array_agg" is an aggregate function
select
TRG.tgname, TFX.proname, pg_get_functiondef(TFX.oid) as fdef
from
pg_trigger TRG
inner join pg_proc TFX on TFX.oid = TRG.tgfoid
where
TRG.tgisinternal = true
and
pg_get_functiondef(TFX.oid) = 'whatever'
Can you help me understand why this blows up ?
I am running "PostgreSQL 9.1.2, compiled by Visual C++ build 1500, 64-bit"
-dvs-
<david.sahagian@emc.com> writes:
-- This blows up. -- SQL state: 42809 -- ERROR: "array_agg" is an aggregate function
select
TRG.tgname, TFX.proname, pg_get_functiondef(TFX.oid) as fdef
from
pg_trigger TRG
inner join pg_proc TFX on TFX.oid = TRG.tgfoid
where
TRG.tgisinternal = true
and
pg_get_functiondef(TFX.oid) = 'whatever'
Can you help me understand why this blows up ?
The second part of the WHERE clause can be evaluated against pg_proc
rows for which pg_get_functiondef() will fail.
An easy workaround would be to use TRG.tgfoid instead, so that the WHERE
clause gets pushed down to the other table. There probably shouldn't be
any entries in pg_trigger for which pg_get_functiondef() will fail.
regards, tom lane