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.
"""
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"
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...
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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 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.
With hindsight I understand this perfectly (and I swear I've brought this
up before and subsequently forgot)...
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].
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.
David J.
"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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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:
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...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.
David J.
"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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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...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?
Works for me - I'd pick "active" or "current" here.
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs