"ldh@laurent-hasson.com" <ldh@laurent-hasson.com> writes:
> This code however works (adding public to the search_path):
> SET search_path TO XXX, public;
> select p.oid::regprocedure
> from pg_catalog.pg_proc p
> join pg_catalog.pg_namespace n on p.pronamespace = n.oid
> where p.oid::regprocedure::text='sample(anyelement)'
> and n.nspname = 'public'
The problem is that what regprocedure-to-text produces is search
path dependent: it might be 'sample(anyelement)', or it might be
'public.sample(anyelement)', depending on whether public is in
the search path.
You could perhaps write an OR to try both of those possibilities,
but the whole query seems quite ugly and probably slow, and an
OR would make it even slower. Personally I'd try something like
exists(select 1
from pg_catalog.pg_proc p
join pg_catalog.pg_namespace n on p.pronamespace = n.oid
where n.nspname = 'public'
and p.proname = 'sample'
and p.proargtypes = array['anyelement'::regtype]::oidvector);
which seems to produce a good query plan.
> If this proves to be a bug, I’ll gladly enter the details in the bug system.
It's intentional behavior. Maybe if we were doing regprocedure
over again today, we'd think twice about the conditional schema
qualification ... but that behavior is a couple of decades old,
so I think it's too late to re-litigate it.
regards, tom lane