Re: Cannot find hstore operator

On 1/24/22 08:27, Dominique Devienne wrote:
> On Mon, Jan 24, 2022 at 3:48 PM David G. Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>> On Monday, January 24, 2022, Dominique Devienne <ddevienne(at)gmail(dot)com> 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(dot)klaver(at)aklaver(dot)com