public inbox for [email protected]  

help / color / mirror / Atom feed

From: Dominique Devienne <[email protected]>
To: [email protected]
Subject: set search_path "$owner". And name versus literal for schemas.
Date: Tue, 18 Jun 2024 10:35:21 +0200
Message-ID: <CAFCRh-8Bnh7PMFKjx=wsueTw6-CEbocOOz1SUpFKbvOjPD+shg@mail.gmail.com> (raw)

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: set search_path "$owner". And name versus literal for schemas.
  In-Reply-To: <CAFCRh-8Bnh7PMFKjx=wsueTw6-CEbocOOz1SUpFKbvOjPD+shg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox