RE: Behavior of pg_catalog dependent on search_path: expected or bug?

   >  -----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.