From: | Dominique Devienne <ddevienne(at)gmail(dot)com> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Paul van der Linden <paul(dot)doskabouter(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Cannot find hstore operator |
Date: | 2022-01-24 16:27:50 |
Message-ID: | CAFCRh--=y_ewtKFVDhfWVjkV_pkaMpsYK8gtWY9nBokdEAULUg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2022-01-24 16:39:51 | Re: Cannot find hstore operator |
Previous Message | Tom Dearman | 2022-01-24 14:55:21 | tstzrange on large table gives poor estimate of expected rows |