Thread: 9.5 Create Function Set From Current taken at creation time?
Reading this:
"""
The SET clause causes the specified configuration parameter to be set to the specified value when the function is entered, and then restored to its prior value when the function exits. SET FROM CURRENT saves the session's current value of the parameter as the value to be applied when the function is entered.
"""
I expect the attached script to output "NOTICE: SP : thirdschema", instead it outputs "NOTICE: SP: otherschema"
Either the wording in the docs is poor or this is broken. If the former it would help to provide an example in said docs as to when one would wish to use "FROM CURRENT" to clarify its intended use.
As I presently read it I get to SET LOCAL search_path within my function without any concern about affecting the caller's execution environment. Simply inheriting the caller's search_path and then making changes in-function will result in those changes remaining.
Thanks!
David J.
P.S. I have the EXECUTE in their since that was original need and thought maybe it was a contributing factor...
Attachment
On 8/8/16 5:46 PM, David G. Johnston wrote: > """ > The SET clause causes the specified configuration parameter to be set to > the specified value when the function is entered, and then restored to > its prior value when the function exits. SET FROM CURRENT saves the > session's current value of the parameter as the value to be applied when > the function is entered. > """ > > https://www.postgresql.org/docs/current/static/sql-createfunction.html > > I expect the attached script to output "NOTICE: SP : thirdschema", > instead it outputs "NOTICE: SP: otherschema" I think the piece you are missing is that it saves the setting when the function is created, not when it is run. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Tue, Aug 9, 2016 at 9:10 AM, Peter Eisentraut < peter.eisentraut@2ndquadrant.com> wrote: > On 8/8/16 5:46 PM, David G. Johnston wrote: > > """ > > The SET clause causes the specified configuration parameter to be set t= o > > the specified value when the function is entered, and then restored to > > its prior value when the function exits. SET FROM CURRENT saves the > > session's current value of the parameter as the value to be applied whe= n > > the function is entered. > > """ > > > > https://www.postgresql.org/docs/current/static/sql-createfunction.html > > > > I expect the attached script to output "NOTICE: SP : thirdschema", > > instead it outputs "NOTICE: SP: otherschema" > > I think the piece you are missing is that it saves the setting when the > function is created, not when it is run. > With hindsight =E2=80=8BI understand this perfectly (and I swear I've broug= ht this up before and subsequently forgot)... =E2=80=8BCan we change the second sentence in the above quote to something = like:=E2=80=8B =E2=80=8BSET FROM CURRENT is a convenience expression to capture the result= of current_setting('setting name') at the time of the function's creation. [if more than just convenience an example of when this is useful would be nice]= . =E2=80=8B The fact that there is no way obtain the useful "restored to its prior value" behavior without specifying an explicit value is a short-coming, not a bug. That said, changing search_paths in a function is likely asking for trouble anyway so the lack probably isn't something worth fixing. =E2=80=8BDavid J.=E2=80=8B =E2=80=8B
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> Can we change the second sentence in the above quote to something like:â
> SET FROM CURRENT is a convenience expression to capture the result of
> current_setting('setting name') at the time of the function's creation. [if
> more than just convenience an example of when this is useful would be nice].
"Convenience" normally means that there's some other way to get the same
result. I do not know of one, and I doubt we'd have introduced the
feature if there was.
regards, tom lane
On Tue, Aug 9, 2016 at 9:43 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > Can we change the second sentence in the above quote to something like:=
=E2=80=8B
>
> > SET FROM CURRENT is a convenience expression to capture the result of
> > current_setting('setting name') at the time of the function's creation.
> [if
> > more than just convenience an example of when this is useful would be
> nice].
>
> "Convenience" normally means that there's some other way to get the same
> result. I do not know of one, and I doubt we'd have introduced the
> feature if there was.
>
Fair enough...=E2=80=8Bso:
SET FROM CURRENT saves the
=E2=80=8B[creating] =E2=80=8B
session's current value of the parameter as the value to be applied when
the function is entered.
=E2=80=8BDavid J.
=E2=80=8B
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> Fair enough...âso:
> SET FROM CURRENT saves the
> â[creating] â
> session's current value of the parameter as the value to be applied when
> the function is entered.
How about "... saves the value of the parameter that is current when
CREATE FUNCTION is executed as the value to be applied ..." ?
Or maybe "active" in place of "current" in that wording?
regards, tom lane
On Tue, Aug 9, 2016 at 1:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "David G. Johnston" <david.g.johnston@gmail.com> writes: > > Fair enough...=E2=80=8Bso: > > > SET FROM CURRENT saves the > > =E2=80=8B[creating] =E2=80=8B > > session's current value of the parameter as the value to be applied whe= n > > the function is entered. > > How about "... saves the value of the parameter that is current when > CREATE FUNCTION is executed as the value to be applied ..." ? > > Or maybe "active" in place of "current" in that wording? > Works for me - =E2=80=8BI'd pick "active" or "current" here=E2=80=8B. Thanks! David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tue, Aug 9, 2016 at 1:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> How about "... saves the value of the parameter that is current when
>> CREATE FUNCTION is executed as the value to be applied ..." ?
>>
>> Or maybe "active" in place of "current" in that wording?
> Works for me - âI'd pick "active" or "current" hereâ.
Done.
regards, tom lane