Re: [HACKERS] Issues for named/mixed function notation patch
On Oct 7, 2009, at 7:41 PM, Tom Lane wrote: Pavel Stehule pavel.steh...@gmail.com writes: [ latest named-args patch ] Committed with a fair amount of corner-case cleanup and refactoring. Woot! Thanks for all the hard work getting this committed (Pavel, Bernd, Jeff, Tom and others)! I've been really looking forward to this feature. Still hoping a solution is found to the plpgsql parser issue. If not, I'll have to resubmit my rejected AS patch. :) -Steve -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PATCH: make plpgsql IN args mutable (v1)
Thank you! -Steve On Sep 19, 2009, at 6:55 PM, Tom Lane wrote: Steve Prentice prent...@cisco.com writes: This patch changes plpgsql IN parameters so they are mutable. I've applied this, since the consensus seemed to be in favor of it. I decided not to update the docs for this change because the docs don't currently indicate that an IN parameter is constant and I didn't want to encourage it because it isn't universally considered good programming practice to assign to an IN parameter. If others think we need a doc change for this, I'll update the patch. I agree, no need to say anything one way or the other in the plpgsql docs. We'll want to mention it in the release notes of course. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PATCH: make plpgsql IN args mutable (v1) [REVIEW]
On Sep 16, 2009, at 6:03 AM, Andrew Dunstan wrote: Abhijit Menon-Sen wrote: At 2009-09-16 08:37:40 -0400, and...@dunslane.net wrote: How does this compare with PLSQL? I don't remember anything of PL/SQL myself, but Pavel Stehule had this to say in response to the original post: This behave is in conflict with PL/SQL, what should do some problems. I thing, so I understand well, why this behave is in PL/SQL. It hasn't sense in plpgsql, because OUT and INOUT params has little bit different syntax (calling) and nobody will do similar bugs (perhaps). What is interesting - this behave is in conformity with SQL/PSM, where parameters are mutable too. I am for it. PL/pgSQL doesn't promise compatibility with PL/SQL and this change should to help some beginners (and this limit is artificial and unnecessary). Given the existing OUT/INOUT syntax difference as noted, I don't think the patch represents a significant problem. I'm not terribly impressed by either of Pavel's arguments. SQL/PSM is irrelevant, and the existence of one inconsistency doesn't seems to me to be a good rationale to create another. If there were a major increase in utility I would be more willing, but at best this overcomes a minor inconvenience, that is easily worked around. It probably won't cause any problem with code being migrated from PLSQL, but it will affect code going the other way. The question is: do we care about that? I'm prepared to be persuaded that we shouldn't care, but I'm not quite there yet. My motivation for submitting the patch was that it makes porting a huge collection of Informix SPL stored procedures easier. There are so many differences between plpgsql and SPL that you would think this wasn't that big of a deal, however, most of the other issues are easily taken care of with a simple sed script or something slightly more advanced (e.g. dealing with the declare/define block differences). This is one of the few compatibility issues where you really need to review and change lots of code by hand. The patch doesn't break existing code and doesn't make it any harder to port code from PL/SQL and on the flip side, this patch with the named/mixed notation patch from Pavel makes porting from Informix's SPL much easier. Thanks for everyone's consideration. -Steve -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PATCH: make plpgsql IN args mutable (v1) [REVIEW]
On Sep 16, 2009, at 8:49 AM, Merlin Moncure wrote: On Wed, Sep 16, 2009 at 8:59 AM, Robert Haas robertmh...@gmail.com wrote: At worst it's an upward-compatible extension, or am I wrong? If it's useful, which I think it is, what's the harm? are we guarding against cases like: select _foo, adjust_foo(_foo) from bar; -- adjust_foo is inout Two things: 1) the patch only affects IN parameters, 2) the parameter is a local copy and doesn't affect parameters/ variables outside of its scope. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PATCH: make plpgsql IN args mutable (v1) [REVIEW]
On Sep 16, 2009, at 12:44 PM, Michael Glaesemann wrote: Certainly. I was doing that to have a simple example; obviously you wouldn't write a mod funciton, and you wouldn't do it in plpgsql. There are other case where the lack of mutability in IN parameters causes you to create a throwaway variable. Have an example at hand? I'd argue that in a case of a function of more complexity from a code clarity standpoint you'd want to assign to a new variable that describes what the new value reflects. I can't say I disagree with you from a purist standpoint, but for porting existing code sometimes it's more efficient to port what you have without rewriting it. In some of the code I'm looking at porting, this is a very simple example of a common pattern I'm seeing: create function create_some_object(pobjectid uuid, psomefkobjectid uuid) returns uuid as $$ begin if pobjectid is null then pobjectid := newid() end if if psomefkobjectid is null then select objectid into psomefkobjectid from somefktable where whatever; end if -- create the object return pobjectid end; -Steve -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] 8.5 plpgsql change for named notation: treat word following AS keyword as label v3
On Sep 13, 2009, at 10:22 PM, Pavel Stehule wrote: 2009/9/14 Tom Lane t...@sss.pgh.pa.us: Robert Haas robertmh...@gmail.com writes: So, I guess I'm sadly left feeling that we should probably reject this patch. Anyone want to argue otherwise? +1. I'm really hoping to get something done about the plpgsql parsing situation before 8.5 is out, so this should be a dead end anyway. I have a WIP patch for integration main SQL parser to plpgsql. I'll send it to this weekend. I certainly don't mind the patch getting rejected and agree that refactoring the plpgsql parser is probably the best approach to this issue. However, I think it would be more than a little strange to ship the named notation feature without a solution for this problem. For reference, the problem is that the function below causes a compile error because of the way plpgsql blindly does variable replacement: create function fun1(pDisplayName text) returns void as $$ begin perform fun2(pDisplayName as pDisplayName); -- Above line compiles as: -- SELECT fun2( $1 as $1 ) end $$ language plpgsql; -Steve -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] mixed, named notation support
On Aug 6, 2009, at 7:12 PM, Robert Haas wrote: On Thu, Aug 6, 2009 at 7:10 PM, Tom Lanet...@sss.pgh.pa.us wrote: Bernd Helmle maili...@oopsware.de writes: Here again a patch version with updated documentation. I will stop reviewing this patch now and mark this ready for committer, so we have some time left to incorporate additional feedback. I'm starting to look at this now, and my very first reaction was what in the world is a leaky list?. I'm not sure I like the data structure itself, but the terminology is certainly completely unhelpful. Can't you come up with something better than continuous/leaky? Stepping back a bit, are we sure this is a feature we even want to support? It was already pointed out in the thread on Parser's hook based on funccall that SQL:201x may standardize = for this purpose. I realize that's a problem because of the possibility of a user-defined operator named =, but aren't we usually reluctant to adopt syntax that is thought likely to be incompatible with current or future SQL standards? As a newbie to postgresql, I would hope this is a feature that will be supported in the not too distant future. If the standard seems to be moving in the direction of using 'name = value' as the syntax, it does seem like that would be the way we would want to go. If I remember correctly, the main argument for using value AS name was that it wouldn't conflict with current operators AND it would be the most likely way the standard body would go. (There was a long thread back in Dec 08 regarding the syntax that can be referenced if someone wants to read through all of them.) If it looks like the SQL standard will be going the direction of 'name = value', why would we go opposite that? Either way, I think Pavel has proven that it is easy to adjust his patch to support either syntax if a decision is made. -Steve -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] mixed, named notation support
On Aug 3, 2009, at 1:41 AM, Pavel Stehule wrote: I should to wait with Steve patch - I would to add main sql parser into plpgsql - than Steve's patch is unnecessary. But if there will be some problems, then we can use Steve's patch. It is simple - so there are not big problems with commit. I was hoping we could get the small patch into plpgsql during this commitfest. This makes plpgsql recognize 'AS' and not replace named parameter labels with the variable reference. I understand there is an effort underway to redo the plpgsql parser, but getting these two patches in together will allow people to start playing with plpgsql + named parameters at the end the of commitfest when the first alpha is released. (You can use named parameters + plpgsql without this patch, but not without some pretty serious limitations.) Without this patch, this will fail: create function create_user(alias text, display_name text) returns void as $$ BEGIN perform create_alias(alias AS alias); ... END $$ language plpgsql; This is a common pattern for many of the stored procedures we are porting and I'd imagine it's common elsewhere too. If the plpgsql parser patch lands, this patch won't be needed, but it's hard to predict when it will land. As an aside, this pattern really shows how confusing the AS syntax can be for named parameters. Which side is the label and which is the value? Thanks, -Steve -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] mixed, named notation support
On Aug 3, 2009, at 9:38 AM, Robert Haas wrote: I sent several notes adding for all patches to be added to commitfest.postgresql.org prior to the start of CommitFest; AFAIK, this one was never added. Hi Robert, The patch for plpgsql was added as a comment to Pavel's patch. I added it as a comment because it wouldn't make since to commit it or even review it separately. This was done on the wiki before the migration. Perhaps that was not the correct way to add it to the commitfest. If not, my apologies. -Steve -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] PATCH: make plpgsql IN args mutable (v1)
Since I didn't get completely shot out of the water and a couple people seemed to think it was helpful, I'm submitting this patch for consideration in the next commitfest. This patch changes plpgsql IN parameters so they are mutable. Previously, they were being forced constant. This patch modifies the plpgsql.sql regression test and corresponding .out file. The regression test also makes sure the passed in parameter does not get changed in the calling function. I decided not to update the docs for this change because the docs don't currently indicate that an IN parameter is constant and I didn't want to encourage it because it isn't universally considered good programming practice to assign to an IN parameter. If others think we need a doc change for this, I'll update the patch. The following function will compile with this patch: create or replace function param_assign_test(a int, val int) returns void as $$ begin a := val; end $$ language plpgsql; This function would have failed to compile previously. -Steve plpgsql_in_args_mutable-v1.diff Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] RFD: Don't force plpgsql IN parameters to constant
Is there a reason we force plpgsql IN parameters to constant? The reason I ask is because having them mutable would go a long way in easing a port from Informix's SPL. For better or worse, we have a fair amount of code in SPL that does something like: -- pObjectId is an IN parameter IF pObjectId IS NULL THEN pObjectId := newid(); END IF; I understand it may be better to use a different technique here, but we have a substantial amount of SPL (40k lines) and if we could make the IN parameters mutable, it would make my day. Looking at the history of the code, it looks like this has been the way it has been since the beginning. Tom added a comment in 1995 asking why we force the IN parameters to constant, but the why? part of the comment was removed in a later change to support OUT and INOUT parameters. I've attached a patch that would change this behavior. Also, the test2(int) function below works with the patch, but would fail to compile without. I also checked to make sure the parameter wasn't passed by reference and it is not. The test at the bottom returns 't' meaning test2(int) did not change the a variable in test1(). CREATE OR REPLACE FUNCTION test1() RETURNS INT AS $$ DECLARE a INT; BEGIN a := 1; PERFORM test2(a); RETURN a; END $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION test2(a INT) RETURNS VOID AS $$ BEGIN a := 2; END $$ LANGUAGE plpgsql; SELECT test1() = 1; If this change would be acceptable, I'll proceed in finishing the patch by updating docs and adding regression tests. -Steve notconstant.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RFD: Don't force plpgsql IN parameters to constant
On Jul 29, 2009, at 5:26 PM, Robert Haas wrote: Wow. I can imagine about a thousand ways that this could break existing applications. I would not be prepared to bet a dollar that anything I've written would survive the impact unscathed. I have a feeling someone else is going to shoot you out of the water completely, but all I'll say is it would definitely need to be OPTIONAL. I guess I don't get how it would break existing applications. All of the regression tests pass. The parameters are passed as a copy, so it can't modify your variable that you pass in. Perhaps I'm missing something--can you elaborate on how this would break existing applications? -Steve -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RFD: Don't force plpgsql IN parameters to constant
On Jul 29, 2009, at 5:23 PM, Andrew Dunstan wrote: First reaction is that it would mean we could never pass them by reference. I know PLPerl uses in effect pass by copy, but what does PLPgsql do? Isn't this effectively what we accomplish with an IN/OUT parameter? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RFD: Don't force plpgsql IN parameters to constant
On Jul 29, 2009, at 4:55 PM, Steve Prentice wrote: Tom added a comment in 1995 For the record, I meant 2005. -Steve -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] 8.5 plpgsql change for named notation: treat word following AS keyword as label v3
On Jul 17, 2009, at 11:56 AM, Bernd Helmle wrote: it seems there's something broken, patch complains about a broken format. Can you please provide a new diff file? Sorry about that--probably got messed up as I pasted it into the message. I've attached the patch this time. plpgsql_keyword_as.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] 8.5 plpgsql change for named notation: treat word following AS keyword as label v2
Hello,
This patch is intended to supplement Pavel's patch for named and mixed
notation support in 8.5. This patch makes it so a plpgsql function can
call another function with the same parameter names using the named
parameters notation. Without this patch, the following example will
have a syntax errors:
CREATE FUNCTION fun1(a INT DEFAULT 1) RETURNS INT AS 'SELECT $1'
LANGUAGE SQL;
CREATE FUNCTION fun2(a INT) RETURNS INT AS $$
DECLARE
t INT;
BEGIN
t := fun1(1 as a); -- syntax error: SELECT fun1(1 as $1 )
t := fun1(a as a); -- syntax error: SELECT fun1( $1 as $1 )
RETURN 0;
END;
$$ LANGUAGE plpgsql;
The patch adds the AS keyword to the plpgsql grammar and doesn't
assign an expression parameter to the sql construct if the scalar
follows the AS keyword.
(v1 of this patch was in the plpgsql + named parameters thread, but
it didn't include the doc changes.)
-Steve
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 80dbf45..9b99314 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -3510,7 +3510,7 @@ PREPARE replaceablestatement_name/(text,
timestamp) AS
/para
para
-emphasisThe substitution mechanism will replace any token that
matches a
+emphasisThe substitution mechanism will replace most tokens
that match a
known variable's name./ This poses various traps for the unwary.
For example, it is a bad idea
to use a variable name that is the same as any table or column name
@@ -3601,9 +3601,29 @@ CONTEXT: SQL statement in PL/PgSQL function
logfunc2 near line 5
interpreted the quotewrong/ way. But it is useful for
clarifying
the intent of potentially-ambiguous code.
/para
-
+
+ para
+There are two places where variable substitution does not happen.
+ /para
+
+ para
+Any label following the AS keyword is not replace. This allows
passing
+parameters by name to functions that have parameters of the same
name as
+the calling function. For example,
+programlisting
+CREATE FUNCTION logfunc(v_logtxt text) RETURNS void AS $$
+BEGIN
+INSERT INTO logtable (logtxt) VALUES (v_logtxt);
+PERFORM tracefunc(v_logtxt AS v_logtxt);
+END;
+ $$ LANGUAGE plpgsql;
+/programlisting
+ All occurances of v_logtxt in the function are replaced except the
one
+ following AS.
+ /para
+
para
-Variable substitution does not happen in the command string given
+Variable substitution also does not happen in the command string
given
to commandEXECUTE/ or one of its variants. If you need to
insert a varying value into such a command, do so as part of
constructing the string value, as illustrated in
diff --git a/src/pl/plpgsql/src/gram.y b/src/pl/plpgsql/src/gram.y
index 06704cf..647daab 100644
--- a/src/pl/plpgsql/src/gram.y
+++ b/src/pl/plpgsql/src/gram.y
@@ -177,6 +177,7 @@ static List
*read_raise_options(void);
* Keyword tokens
*/
%token K_ALIAS
+%token K_AS
%token K_ASSIGN
%token K_BEGIN
%token K_BY
@@ -1977,6 +1978,7 @@ read_sql_construct(int until,
int *endtoken)
{
int tok;
+ int prevtok = 0;
int lno;
PLpgSQL_dstring ds;
int parenlevel = 0;
@@ -1989,7 +1991,7 @@ read_sql_construct(int until,
plpgsql_dstring_init(ds);
plpgsql_dstring_append(ds, sqlstart);
- for (;;)
+ for (;;prevtok = tok)
{
tok = yylex();
if (tok == until parenlevel == 0)
@@ -2034,10 +2036,16 @@ read_sql_construct(int until,
switch (tok)
{
case T_SCALAR:
- snprintf(buf, sizeof(buf), $%d ,
-
assign_expr_param(yylval.scalar-dno,
-
params, nparams));
- plpgsql_dstring_append(ds, buf);
+ /* A scalar following AS is treated as a label
*/
+ if (prevtok == K_AS)
+ plpgsql_dstring_append(ds, yytext);
+ else
+ {
+ snprintf(buf, sizeof(buf), $%d ,
+
assign_expr_param(yylval.scalar-dno,
+
params, nparams));
+ plpgsql_dstring_append(ds, buf);
+ }
break;
case
Re: [HACKERS] [PATCH] 8.5 plpgsql change for named notation: treat word following AS keyword as label v3
On May 21, 2009, at 10:52 AM, Tom Lane wrote:
It's probably time to bite the bullet and redo the parser as has been
suggested in the past, ie fix things so that the main parser is used.
Ideally I'd like to switch the name resolution priority to be more
Oracle-like, but even if we don't do that it would be a great
improvement to have actual syntactic knowledge behind the lookups.
That kind of refactoring is beyond my experience-level with the code,
but I can't say I disagree with your analysis.
Just for the record, you'd have to put the same kluge into the
T_RECORD
and T_ROW cases if we wanted to do it like this.
Patch updated.
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 80dbf45..f8e8ce4 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -3510,7 +3510,7 @@ PREPARE replaceablestatement_name/(text,
timestamp) AS
/para
para
-emphasisThe substitution mechanism will replace any token that
matches a
+emphasisThe substitution mechanism will replace most tokens
that match a
known variable's name./ This poses various traps for the
unwary.
For example, it is a bad idea
to use a variable name that is the same as any table or column
name
@@ -3603,7 +3603,27 @@ CONTEXT: SQL statement in PL/PgSQL function
logfunc2 near line 5
/para
para
-Variable substitution does not happen in the command string given
+There are two places where variable substitution does not happen.
+ /para
+
+ para
+Any label following the AS keyword is not replaced. This allows
passing
+parameters by name to functions that have parameters of the same
name as
+the calling function. For example,
+programlisting
+CREATE FUNCTION logfunc(v_logtxt text) RETURNS void AS $$
+BEGIN
+INSERT INTO logtable (logtxt) VALUES (v_logtxt);
+PERFORM tracefunc(v_logtxt AS v_logtxt);
+END;
+ $$ LANGUAGE plpgsql;
+/programlisting
+ All occurances of v_logtxt in the function are replaced except the
one
+ following AS.
+ /para
+
+ para
+Variable substitution also does not happen in the command string
given
to commandEXECUTE/ or one of its variants. If you need to
insert a varying value into such a command, do so as part of
constructing the string value, as illustrated in
diff --git a/src/pl/plpgsql/src/gram.y b/src/pl/plpgsql/src/gram.y
index 06704cf..3b4e9b8 100644
--- a/src/pl/plpgsql/src/gram.y
+++ b/src/pl/plpgsql/src/gram.y
@@ -177,6 +177,7 @@ static List
*read_raise_options(void);
* Keyword tokens
*/
%token K_ALIAS
+%token K_AS
%token K_ASSIGN
%token K_BEGIN
%token K_BY
@@ -1977,6 +1978,7 @@ read_sql_construct(int until,
int *endtoken)
{
int tok;
+ int prevtok = 0;
int lno;
PLpgSQL_dstring ds;
int parenlevel = 0;
@@ -1989,7 +1991,7 @@ read_sql_construct(int until,
plpgsql_dstring_init(ds);
plpgsql_dstring_append(ds, sqlstart);
- for (;;)
+ for (;;prevtok = tok)
{
tok = yylex();
if (tok == until parenlevel == 0)
@@ -2031,6 +2033,16 @@ read_sql_construct(int until,
if (plpgsql_SpaceScanned)
plpgsql_dstring_append(ds, );
+ /* A variable following AS is treated as a label */
+ if (prevtok == K_AS
+ (tok == T_SCALAR || tok == T_ROW ||
tok == T_RECORD))
+ {
+ plpgsql_dstring_append(ds, yytext);
+ continue;
+ }
+
switch (tok)
{
case T_SCALAR:
diff --git a/src/pl/plpgsql/src/scan.l b/src/pl/plpgsql/src/scan.l
index 1917eef..e3a5c45 100644
--- a/src/pl/plpgsql/src/scan.l
+++ b/src/pl/plpgsql/src/scan.l
@@ -149,6 +149,7 @@ param \${digit}+
= { return
K_ASSIGN; }
\.\. { return K_DOTDOT; }
alias { return K_ALIAS; }
+as { return
K_AS; }
begin { return K_BEGIN; }
by { return
K_BY; }
case { return K_CASE;}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] 8.5 plpgsql change for named notation: treat word following AS keyword as label v2
= is more common than as for sure, but this has been talked about before starting at about this message: http://archives.postgresql.org/message-id/14319.1228833...@sss.pgh.pa.us -Steve On May 21, 2009, at 11:51 AM, Josh Berkus wrote: The patch adds the AS keyword to the plpgsql grammar and doesn't assign an expression parameter to the sql construct if the scalar follows the AS keyword. Would it be possible to also support = as well as as? I believe that SQL Server uses = exclusively, and supporting that syntax would help people port TSQL-based applications. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] plpgsql + named parameters
t := fun1(1 as a); -- syntax error: SELECT fun1(1 as $1 ) t := fun1(a as a); -- syntax error: SELECT fun1( $1 as $1 ) On May 19, 2009, at 6:42 PM, Merlin Moncure wrote: you have a name conflict here...is it deliberate? I've learned the hard way to always, always prefix arguments and locals to plpgsql functions with '_'. Or are you trying to do something fancier? The conflict is deliberate to illustrate the limitations the named parameter feature (on the list for the first 8.5 CommitFest) is going to have if parameter substitution is not addressed at the same time. -Steve
Re: [HACKERS] plpgsql + named parameters
On May 20, 2009, at 10:24 AM, Pavel Stehule wrote:
this problem is little bit deeper and is related to plpgsql method for
SQL query processing.
I thing so there are two solutions:
a) use dynamic SQL
b) use double quotes for identifier - identifiers have to be lower
t := fun1(a as a);
plpgsql substitutes an expression parameter for the double-quoted
identifier as well and I'm less than thrilled about using dynamic SQL
to make all my function calls. I was hoping we could modify the
grammar so that identifiers after the AS keyword are passed through.
Something like this patch:
diff --git a/src/pl/plpgsql/src/gram.y b/src/pl/plpgsql/src/gram.y
index 06704cf..66d12d8 100644
--- a/src/pl/plpgsql/src/gram.y
+++ b/src/pl/plpgsql/src/gram.y
@@ -177,6 +177,7 @@ static List
*read_raise_options(void);
* Keyword tokens
*/
%token K_ALIAS
+%token K_AS
%token K_ASSIGN
%token K_BEGIN
%token K_BY
@@ -1977,6 +1978,7 @@ read_sql_construct(int until,
int *endtoken)
{
int tok;
+ int prevtok = 0;
int lno;
PLpgSQL_dstring ds;
int parenlevel = 0;
@@ -1989,7 +1991,7 @@ read_sql_construct(int until,
plpgsql_dstring_init(ds);
plpgsql_dstring_append(ds, sqlstart);
- for (;;)
+ for (;;prevtok = tok)
{
tok = yylex();
if (tok == until parenlevel == 0)
@@ -2034,10 +2036,22 @@ read_sql_construct(int until,
switch (tok)
{
case T_SCALAR:
- snprintf(buf, sizeof(buf), $%d ,
-
assign_expr_param(yylval.scalar-dno,
-
params, nparams));
- plpgsql_dstring_append(ds, buf);
+ /*
+* If the previous token is AS, then we pass
the scalar
+* through as a label. Otherwise, make the
scalar an
+* expression parameter.
+*/
+ if (prevtok == K_AS)
+ {
+ plpgsql_dstring_append(ds, yytext);
+ }
+ else
+ {
+ snprintf(buf, sizeof(buf), $%d ,
+
assign_expr_param(yylval.scalar-dno,
+
params, nparams));
+ plpgsql_dstring_append(ds, buf);
+ }
break;
case T_ROW:
diff --git a/src/pl/plpgsql/src/scan.l b/src/pl/plpgsql/src/scan.l
index 1917eef..e3a5c45 100644
--- a/src/pl/plpgsql/src/scan.l
+++ b/src/pl/plpgsql/src/scan.l
@@ -149,6 +149,7 @@ param \${digit}+
= { return K_ASSIGN; }
\.\.{ return K_DOTDOT; }
alias { return K_ALIAS; }
+as { return K_AS; }
begin { return K_BEGIN; }
by { return K_BY; }
case{ return K_CASE;}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] plpgsql + named parameters
I followed the past discussions regarding the syntax for named parameters and I am currently using Pavel Stehule's patch for named and mixed notation on top of the 8.4 beta. It seems the way plpgsql substitutes $1, $2, etc for the parameters is going to reduce the usefulness of this feature. Consider these two functions: CREATE FUNCTION fun1(a INT DEFAULT 1) RETURNS INT AS 'SELECT $1' LANGUAGE SQL; CREATE FUNCTION fun2(a INT) RETURNS INT AS $$ DECLARE t INT; BEGIN t := fun1(1 as a); -- syntax error: SELECT fun1(1 as $1 ) t := fun1(a as a); -- syntax error: SELECT fun1( $1 as $1 ) RETURN 0; END; $$ LANGUAGE plpgsql; I would think this would be a very common scenario where one function calls another similar function that has similar parameter names. Am I missing something or are there any obvious solutions to this? Pavel's patch: http://archives.postgresql.org/message-id/162867790903042341o477b115dtb6b351dd8ff75...@mail.gmail.com Thanks, -Steve -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
