Re: BUG #11078: this query crash on array_agg, but there is no array_agg

hiapo(at)tahiti-ingenierie(dot)pf writes:
> select x.oid,x.c from (
> select p.oid,pg_get_functiondef(p.oid)::varchar as c
> from information_schema.routines r inner join pg_catalog.pg_proc p on
> (r.specific_name=p.proname||'_'||oid::varchar) where
> specific_schema='public'
> )x where x.c ilike '%getcnx%'

pg_get_functiondef doesn't work on aggregate functions. You might get
away with not defending against that without the ilike condition; but
with it, the ilike gets pushed down to the scan of pg_proc so the
join condition doesn't save you.

You could do something like this to avoid the problem:

select x.oid,x.c from (
select p.oid,case when p.proisagg then null else pg_get_functiondef(p.oid) end as c
from information_schema.routines r inner join pg_catalog.pg_proc p on
(r.specific_name=p.proname||'_'||oid::varchar) where
specific_schema='public'
)x where x.c ilike '%getcnx%';

Another idea, depending on what you are really trying to accomplish with
the ilike, is to just look directly at p.prosrc instead of going through
pg_get_functiondef.

regards, tom lane