> -----Original Message-----
> From: Tom Lane <tgl@sss.pgh.pa.us>
> Sent: Sunday, February 5, 2023 00:35
> To: ldh@laurent-hasson.com
> Cc: pgsql-bugs@lists.postgresql.org
> Subject: Re: Behavior of pg_catalog dependent on search_path: expected
> or bug?
>
> "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
Thank you so much! And David too. I was surprised by that behavior and thought it might be a bug, but now I understand
whyand it's been what it is for a long time. Thank you for the enhanced query too. I will try it asap.
Laurent.
