Re: set search_path "$owner". And name versus literal for schemas.

On 6/18/24 01:35, Dominique Devienne wrote:
> Hi. Two things related to the search_path.
>
> First, [the doc][1] mentions one can use a variable like "$user" for
> the search_path. But setting the search_path is also for FUNCTIONs and
> PROCEDUREs, and there what I really REALLY would like, is the ability
> to use "$owner", to limit the search_path to the OWNER schema of that
> func/proc, instead of having to explicitly spell it out. When I want
> to *clone* a schema, having to "patch" the search_path of all those
> funcs/procs (to replace the old schema with the new one), is a real
> PITA.
>
> Has this ever been considered? And if so, why was it refused?
> It would simplify my life so much, I wonder why this doesn't already exist.

I could see this. I would choose something other then $owner as you are
not really concerned with the func/proc owner but it's location.
Something like $home would seem more on point.

>
> Second, and related to the first point, when I introspect a schema,
> the search_path
> of functions/procedures seems to be rewritten with literals, instead of names.
> Even the doc uses names, so why is it rewritten as literals? Or
> accepts both in fact.
>
> To actually simplify schema cloning, the introspected proc/func
> search_path should remain "$owner" (or '$owner' I guess...) and not be
> expanded. Otherwise we'd back to "manual" patching of the search_path,
> which again is a PITA.
>
> I'm curious to hear/read what PostgreSQL experts have to say on this subject.
>
> Thanks, --DD
>
> [1]: https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com