On Mon, 24 Jan 2022, 3:22 pm Dominique Devienne, <ddevie...@gmail.com> wrote:
> Hi. In > https://www.mail-archive.com/pgsql-general@lists.postgresql.org/msg29321.html > I asked: > > > On Tue, Jan 11, 2022 at 5:30 PM David G. Johnston < > david.g.johns...@gmail.com> wrote: > > > On Tuesday, January 11, 2022, Dominique Devienne <ddevie...@gmail.com>> > wrote: > > >> This means the template-schema name is part of the DDL for the schema, > > >> and a clone would need to use its own search-path, not the original. > > > This is your major limitation. You are required to create new objects > > > from code and cannot leverage any kind of copy of existing objects. > > > > But how to avoid that limitation? > > > > Triggers in a schema should functions correctly, whether or not client > > sessions set the search_path, or use fully qualified object names. > > I was actually surprised that functions from the schema itself (where the > > trigger is defined), do "not bind more tightly" to the dot (.) schema, > > the "owner" schema of the trigger, compared to functions elsewhere. > > > > Perhaps there's something I'm missing around trigger and name resolution? > > But didn't any answer at the time. > > But Tom's answer to Paul's question seems to be related to my original > question, no? > > On Sun, Jan 23, 2022 at 4:22 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > > Paul van der Linden <paul.doskabou...@gmail.com> writes: > > > Thanks for the clarification, but giving up performance is a no-go for > us. > > > Also I have my concerns about shemaqualifying each and every use of > the -> > > > operator, there are really a lot of them in my functions and it would > > > severely impact readability. Are these the only 2 solutions possible? > > > > As of v14 you could use SQL-style function definitions, so that the > > operator is parsed at function definition time instead of runtime. > > 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: > ``` > CREATE OR REPLACE FUNCTION attributeinput_insert_app_time_ext_tf() > RETURNS trigger > LANGUAGE plpgsql > SET search_path TO owner_schema_of_fn_tg_tab, 'pg_temp' > AS $function$ > BEGIN > UPDATE AttributeInput > SET AppCreateDate = NEW.CreateDate > WHERE Guid = NEW.Guid; > RETURN NEW; > END; > $function$ > ``` > can be re-written as below? > ``` > CREATE OR REPLACE FUNCTION attributeinput_insert_app_time_ext_tf() > RETURNS trigger > LANGUAGE sql > BEGIN ATOMIC > UPDATE AttributeInput > SET AppCreateDate = NEW.CreateDate > WHERE Guid = NEW.Guid; > RETURN NEW; > END; > ``` > As long as owner_schema_of_fn_tg_tab is first in the search_path at > function-creation time? > Or does the v14-specific trick Tom mentioned is not available to > trigger-returning functions? > I'm kinda afraid that RETUR NEW; is specific to plpgsql... > > I'm still on v12, so cannot test v14 yet. We planned to move to v14, > for lz4 and built-in uuid-creation function, > but if we could get rid of the `SET search_path` workaround in our > trigger functions, that would be even more motivation. > > 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? > > Thanks, --DD > This might help. Alter user <user name> SET search_path TO myschema,public; No need to set search_path every time. Regards, Ganesh Korde.
