Обсуждение: BUG #11078: this query crash on array_agg, but there is no array_agg

The following bug has been logged on the website:

Bug reference:      11078
Logged by:          hiapo
Email address:      hiapo@tahiti-ingenierie.pf
PostgreSQL version: 9.3.3
Operating system:   Linux OpenSuse 12.3
Description:




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%'

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