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.klaver@xxxxxxxxxxx
