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 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? 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?
