MIME-Version: 1.0
From: Dominique Devienne <ddevienne@gmail.com>
Date: Tue, 18 Jun 2024 10:35:21 +0200
Message-ID: <CAFCRh-8Bnh7PMFKjx=wsueTw6-CEbocOOz1SUpFKbvOjPD+shg@mail.gmail.com>
Subject: set search_path "$owner". And name versus literal for schemas.
To: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAFCRh-8Bnh7PMFKjx%3DwsueTw6-CEbocOOz1SUpFKbvOjPD%2Bshg%40mail.gmail.com>
Precedence: bulk

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.

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