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

hiapo@tahiti-ingenierie.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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs