Re: Cannot find hstore operator

On Mon, 24 Jan 2022, 3:22 pm Dominique Devienne, <ddevienne(at)gmail(dot)com>
wrote:

> Hi. In
> https://www.mail-archive.com/pgsql-general(at)lists(dot)postgresql(dot)org/msg29321.html
> I asked:
>
> > On Tue, Jan 11, 2022 at 5:30 PM David G. Johnston <
> david(dot)g(dot)johns(dot)(dot)(dot)(at)gmail(dot)com> wrote:
> > > On Tuesday, January 11, 2022, Dominique Devienne <ddevie(dot)(dot)(dot)(at)gmail(dot)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 <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Paul van der Linden <paul(dot)doskabouter(at)gmail(dot)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.