Thread: 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