Re: Cannot find hstore operator - Mailing list pgsql-general

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 <tgl@sss.pgh.pa.us> wrote:
> Paul van der Linden <paul.doskabouter@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