On 1/24/22 08:27, Dominique Devienne wrote:
On Mon, Jan 24, 2022 at 3:48 PM David G. Johnston
<david.g.johnston@xxxxxxxxx> wrote:
On Monday, January 24, 2022, Dominique Devienne <ddevienne@xxxxxxxxx> wrote:
After re-reading
https://www.postgresql.org/docs/14/sql-createfunction.html in light of
Tom's answer,
does that mean that our `SET search_path TO {0}, 'pg_temp'`
workaround, in the trigger below,
to not depend on the search_path at runtime:
The general trigger documentation is kind enough to point out that the sql language cannot be used to write trigger functions.
OK, failr enough...
But what about:
I'd really like my DDL statements to NOT contain schema-specific names,
to ensure proper name resolution independent of the search_path and
completely "self-contained" in the schema itself.
Is there any way to achieve that, beside our current `SET search_path` workaround?
Could I please get a definitive answer about this?
David, in the "clone-schema" thread, you kind of implied I shouldn't
have a set search_path in the triggers,
thus making my DDL schema-specific, but then when I ask about ways to
If I am following your code and objects are schema specific so there is
no way to avoid this.
avoid thus, and have "static" resolution
of names in those trigger functions, I'm not getting alternatives.
Am I the only one to think that a session w/o a seach_path, which
fully qualifies table names,
should behaves exactly the same way than another session that has a
search_path and does not fully qualify table names?
Objects end up being found in a schema, either you specify that schema
when using the object or the server walks the search_path to find the
first schema where an object with the name exists.
Because that's the only reason I added a set search_path to our
trigger functions. The alternative being to fully-qualify
all object references in those trigger functions, making the DDL even
more "schema-specific". it feels like a catch-22...
I'm not trying to be difficult here... I'm trying to understand, FWIW. --DD
PS: Does INVOKER vs DEFINER affect name resolution? Does that apply to
trigger functions?
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
