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