Thread: PL/pgSQL 'i = i + 1' Syntax
Hellow PostgreSQL hackers, Quick question. Why does the 'i = i + 1' syntax work in this PL/pgSQL function? try=# CREATE OR REPLACE FUNCTION inc_by_two( try(# upfrom int, try(# upto int try(# ) RETURNS SETOF INT AS $$ try$# BEGIN try$# FOR i IN upfrom..upto LOOP try$# RETURN NEXT i; try$# i = i + 1; try$# END LOOP; try$# END; try$# $$ LANGUAGE 'plpgsql'; CREATE FUNCTION try=# select * from inc_by_two(1,10); inc_by_two ------------ 1 3 5 7 9 (5 rows) Someone posted a PL/pgSQL function in my blog with this syntax, which is how I know about it, but I couldn't find it documented anywhere. Is it a special exception for loop variables, perhaps? Thanks, David
David Wheeler wrote: > Hellow PostgreSQL hackers, > > Quick question. Why does the 'i = i + 1' syntax work in this PL/pgSQL > function? > > try=# CREATE OR REPLACE FUNCTION inc_by_two( > try(# upfrom int, > try(# upto int > try(# ) RETURNS SETOF INT AS $$ > try$# BEGIN > try$# FOR i IN upfrom..upto LOOP > try$# RETURN NEXT i; > try$# i = i + 1; > try$# END LOOP; > try$# END; > try$# $$ LANGUAGE 'plpgsql'; > CREATE FUNCTION > try=# select * from inc_by_two(1,10); > inc_by_two > ------------ > 1 > 3 > 5 > 7 > 9 > (5 rows) > > Someone posted a PL/pgSQL function in my blog with this syntax, which > is how I know about it, but I couldn't find it documented anywhere. Is > it a special exception for loop variables, perhaps? > It ought to be illegal to modify the loop control variable anyway, IMNSHO - it certainly is in Ada, the distant antecedent of pl/pgsql. cheers andrew
David Wheeler wrote: > Hellow PostgreSQL hackers, > > Quick question. Why does the 'i = i + 1' syntax work in this PL/pgSQL > function? > > try=# CREATE OR REPLACE FUNCTION inc_by_two( > try(# upfrom int, > try(# upto int > try(# ) RETURNS SETOF INT AS $$ > try$# BEGIN > try$# FOR i IN upfrom..upto LOOP > try$# RETURN NEXT i; > try$# i = i + 1; > try$# END LOOP; > try$# END; > try$# $$ LANGUAGE 'plpgsql'; > CREATE FUNCTION > try=# select * from inc_by_two(1,10); > inc_by_two > ------------ > 1 > 3 > 5 > 7 > 9 > (5 rows) > > Someone posted a PL/pgSQL function in my blog with this syntax, which > is how I know about it, but I couldn't find it documented anywhere. Is > it a special exception for loop variables, perhaps? > > Thanks, > > David > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > The syntax for assignment is: i := i + 1 what you are doing is merely comparison. Since you are not using the results of the comparison, it is a no-op. mark
On May 16, 2006, at 16:30, Andrew Dunstan wrote:
> It ought to be illegal to modify the loop control variable anyway,
> IMNSHO - it certainly is in Ada, the distant antecedent of pl/pgsql.
I agree, but I must say that it's incredibly useful to be able to
increment by two as I go through a loop:
FOR i IN 1 + offset .. 11 + offset LOOP total := total + substring(ean, i, 1)::INTEGER; i = i + 1;
ENDLOOP;
Best,
David
Mark Dilger wrote: > David Wheeler wrote: > >>Hellow PostgreSQL hackers, >> >>Quick question. Why does the 'i = i + 1' syntax work in this PL/pgSQL >>function? >> >>try=# CREATE OR REPLACE FUNCTION inc_by_two( >>try(# upfrom int, >>try(# upto int >>try(# ) RETURNS SETOF INT AS $$ >>try$# BEGIN >>try$# FOR i IN upfrom..upto LOOP >>try$# RETURN NEXT i; >>try$# i = i + 1; >>try$# END LOOP; >>try$# END; >>try$# $$ LANGUAGE 'plpgsql'; >>CREATE FUNCTION >>try=# select * from inc_by_two(1,10); >>inc_by_two >>------------ >> 1 >> 3 >> 5 >> 7 >> 9 >>(5 rows) >> >>Someone posted a PL/pgSQL function in my blog with this syntax, which >>is how I know about it, but I couldn't find it documented anywhere. Is >>it a special exception for loop variables, perhaps? >> >>Thanks, >> >>David >> >>---------------------------(end of broadcast)--------------------------- >>TIP 2: Don't 'kill -9' the postmaster >> > > > The syntax for assignment is: > > i := i + 1 > > what you are doing is merely comparison. Since you are not using the results of > the comparison, it is a no-op. > > mark So I don't know why it works for you. I wrote the following, and it also increments the variable: CREATE OR REPLACE FUNCTION weird () RETURNS SETOF INT AS $$ DECLARE i integer; BEGIN i := 0; return next i; i = i + 1; return next i; i = i + 1; return next i; return; END; $$ LANGUAGE plpgsql; So I don't think it has anything to do with loop variables, specifically. mark
On May 16, 2006, at 16:42, Mark Dilger wrote: > So I don't know why it works for you. I wrote the following, and > it also > increments the variable: > > CREATE OR REPLACE FUNCTION weird () RETURNS SETOF INT AS $$ > DECLARE > i integer; > BEGIN > i := 0; > return next i; > i = i + 1; > return next i; > i = i + 1; > return next i; > return; > END; > $$ LANGUAGE plpgsql; > > So I don't think it has anything to do with loop variables, > specifically. Indeed. It appears that, contrary to what I previously thought, := also works: CREATE OR REPLACE FUNCTION inc_by_two( upfrom int, upto int ) RETURNS SETOF INT AS $$ BEGIN FOR i IN upfrom..upto LOOP RETURN NEXT i; i := i + 1; END LOOP; END; $$ LANGUAGE 'plpgsql'; try=# select * from inc_by_two(1,11); inc_by_two ------------ 1 3 5 7 9 11 (6 rows) Best, David
On May 16, 2006, at 16:53, Mark Dilger wrote: > Sorry, I meant to say that it should only be a no-op according to > the language > specification, as I understand it. The fact that it works suggests > to me that > the implementation of PL/pgsql has been modified (or broken?). > Whether the > change is a bug or an intentional feature change, I don't know. Ah, yes, I agree, and that's why I posted my query. > mark > > P.S., Looking through the documentation, I don't immediately see > the spec for > how a regular '=' is supposed to work, but assignment is documented > as using the > ':=' syntax and says nothing about '='. Exactly. But I use = all the time for comparision: IF FOUND = TRUE THEN .... END IF Best, David
David Wheeler wrote: > On May 16, 2006, at 16:53, Mark Dilger wrote: > >> Sorry, I meant to say that it should only be a no-op according to the >> language >> specification, as I understand it. The fact that it works suggests >> to me that >> the implementation of PL/pgsql has been modified (or broken?). >> Whether the >> change is a bug or an intentional feature change, I don't know. > > > Ah, yes, I agree, and that's why I posted my query. > >> mark >> >> P.S., Looking through the documentation, I don't immediately see the >> spec for >> how a regular '=' is supposed to work, but assignment is documented >> as using the >> ':=' syntax and says nothing about '='. > > > Exactly. But I use = all the time for comparision: > > IF FOUND = TRUE THEN > .... > END IF > > Best, > > David It seems this has been answered before, by Tom Lane: http://archives.postgresql.org/pgsql-novice/2006-04/msg00138.php
On May 16, 2006, at 17:02, Mark Dilger wrote: > It seems this has been answered before, by Tom Lane: > > http://archives.postgresql.org/pgsql-novice/2006-04/msg00138.php Ah, cool, then it *is* known. Thanks, David
On 5/16/06, David Wheeler <david@kineticode.com> wrote: > On May 16, 2006, at 16:30, Andrew Dunstan wrote: > > > It ought to be illegal to modify the loop control variable anyway, > > IMNSHO - it certainly is in Ada, the distant antecedent of pl/pgsql. > > I agree, but I must say that it's incredibly useful to be able to > increment by two as I go through a loop: > > FOR i IN 1 + offset .. 11 + offset LOOP > total := total + substring(ean, i, 1)::INTEGER; > i = i + 1; > END LOOP; > > Best, > > David > i have posted a patch to add a BY clause to the for statement (integer version), with the BY clause you can specify an increment value... it's in the unapplied patches list waiting for review... http://candle.pha.pa.us/mhonarc/patches/msg00003.html -- regards, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook
On May 16, 2006, at 17:51, Jaime Casanova wrote: > i have posted a patch to add a BY clause to the for statement (integer > version), with the BY clause you can specify an increment value... > > it's in the unapplied patches list waiting for review... > > http://candle.pha.pa.us/mhonarc/patches/msg00003.html Ah, /me likes! Any chance that'll get in for 8.2? Best, David
Andrew Dunstan <andrew@dunslane.net> writes:
> It ought to be illegal to modify the loop control variable anyway,
> IMNSHO - it certainly is in Ada, the distant antecedent of pl/pgsql.
Distant ancestors aren't particularly relevant here. What plpgsql tries
to be is a ripoff^H^H^H^H^H^Hsincere flattery of Oracle's PL/SQL. If
modifying the loop variable is disallowed in PL/SQL, I'm all for
disallowing it in plpgsql, otherwise not. Anyone have a recent copy of
Oracle to try it on?
regards, tom lane
On May 16, 2006, at 19:52, Tom Lane wrote: > Distant ancestors aren't particularly relevant here. What plpgsql > tries > to be is a ripoff^H^H^H^H^H^Hsincere flattery of Oracle's PL/SQL. If > modifying the loop variable is disallowed in PL/SQL, I'm all for > disallowing it in plpgsql, otherwise not. Even if PL/SQL disallows it, why would you not allow it in PL/pgSQL? So that it's easier to migrate from PostgreSQL to Oracle? If you only care about Oracle to PostgreSQL (and who wouldn't?), then it in fact seems desirable for PL/pgSQL to be a superset of PL/SQL. But that's must MYH. Best, David
David Wheeler <david@kineticode.com> writes:
> Even if PL/SQL disallows it, why would you not allow it in PL/pgSQL?
Um ... design sanity and consistency, maybe? Not that I think PL/SQL
is any paragon of those virtues, but surely "we'll invent any feature
we feel like whether it's sane or not" is not a recipe for a
maintainable language.
(No, I'm not particularly in favor of the BY feature mentioned upthread,
either.)
regards, tom lane
On May 16, 2006, at 20:51, Tom Lane wrote: > Um ... design sanity and consistency, maybe? Not that I think PL/SQL > is any paragon of those virtues, but surely "we'll invent any feature > we feel like whether it's sane or not" is not a recipe for a > maintainable language. Yes, sanity is important, I agree. > (No, I'm not particularly in favor of the BY feature mentioned > upthread, > either.) Pity. I thought it was a good idea. Best, David
> (No, I'm not particularly in favor of the BY feature mentioned upthread, > either.) > > regards, tom lane > mmm... and why is that? i mean, many languages have some way to increment the for variable by different values... call it STEP, BY or even i+=number.... -- regards, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook
"Jaime Casanova" <systemguards@gmail.com> writes:
>> (No, I'm not particularly in favor of the BY feature mentioned upthread,
>> either.)
> mmm... and why is that?
Essentially because it's not in the upstream language. Oracle could
come out with the same feature next week, only they use STEP or some
other syntax for it, and then we'd have a mess on our hands. If the
feature were sufficiently compelling use-wise then I'd be willing to
risk that, but it doesn't seem to me to be more than a marginal
notational improvement.
regards, tom lane
Tom Lane wrote: >> It ought to be illegal to modify the loop control variable anyway, >> IMNSHO - it certainly is in Ada, the distant antecedent of pl/pgsql. > > If modifying the loop variable is disallowed in PL/SQL, I'm all for > disallowing it in plpgsql, otherwise not. Anyone have a > recent copy of Oracle to try it on? I tried this on Oracle 10.2.0.2.0 (which is the most recent version): SET SERVEROUTPUT ON BEGIN FOR i IN 1..10 LOOP i := i + 1; DBMS_OUTPUT.PUT_LINE(i); END LOOP; END; / i := i + 1; * ERROR at line 3: ORA-06550: line 3, column 7: PLS-00363: expression 'I' cannot be used as an assignment target ORA-06550: line 3, column 7: PL/SQL: Statement ignored And the documentation also explicitly states that it is not allowed. By the way, PL/SQL screams if you want to do an assignment with '='. But I guess that the current behaviour of PL/pgSQL should not reflect that to maintain backward compatibility, right? Yours, Laurenz Albe
Albe Laurenz said: > Tom Lane wrote: >>> It ought to be illegal to modify the loop control variable anyway, >>> IMNSHO - it certainly is in Ada, the distant antecedent of pl/pgsql. >> >> If modifying the loop variable is disallowed in PL/SQL, I'm all for >> disallowing it in plpgsql, otherwise not. Anyone have a >> recent copy of Oracle to try it on? > > I tried this on Oracle 10.2.0.2.0 (which is the most recent version): > > SET SERVEROUTPUT ON > BEGIN > FOR i IN 1..10 LOOP > i := i + 1; > DBMS_OUTPUT.PUT_LINE(i); > END LOOP; > END; > / > i := i + 1; > * > ERROR at line 3: > ORA-06550: line 3, column 7: > PLS-00363: expression 'I' cannot be used as an assignment target > ORA-06550: line 3, column 7: > PL/SQL: Statement ignored > > And the documentation also explicitly states that it is not allowed. > So should we if it can be done conveniently. That might be a big IF - IIRC many Pascal compilers ignore the similar language rule because implementing it is a pain in the neck. > By the way, PL/SQL screams if you want to do an assignment with '='. > But I guess that the current behaviour of PL/pgSQL should not reflect > that to maintain backward compatibility, right? > I think it should. The current behaviour is undocumented and more than icky. cheers andrew
"Andrew Dunstan" <andrew@dunslane.net> writes:
> Albe Laurenz said:
>> ERROR at line 3:
>> ORA-06550: line 3, column 7:
>> PLS-00363: expression 'I' cannot be used as an assignment target
>> ORA-06550: line 3, column 7:
>> PL/SQL: Statement ignored
>>
>> And the documentation also explicitly states that it is not allowed.
> So should we if it can be done conveniently. That might be a big IF - IIRC
> many Pascal compilers ignore the similar language rule because implementing
> it is a pain in the neck.
Since we already have the notion of a "const" variable in plpgsql,
I think it might work to just mark the loop variable as const.
>> By the way, PL/SQL screams if you want to do an assignment with '='.
>> But I guess that the current behaviour of PL/pgSQL should not reflect
>> that to maintain backward compatibility, right?
> I think it should. The current behaviour is undocumented and more than icky.
The lack of documentation is easily fixed ;-). I don't think this is
icky enough to justify breaking all the existing functions we'd
undoubtedly break if we changed it.
regards, tom lane
Tom Lane wrote: >>> By the way, PL/SQL screams if you want to do an assignment with '='. >>> But I guess that the current behaviour of PL/pgSQL should not reflect >>> that to maintain backward compatibility, right? >>> > > >> I think it should. The current behaviour is undocumented and more than icky. >> > > The lack of documentation is easily fixed ;-). I don't think this is > icky enough to justify breaking all the existing functions we'd > undoubtedly break if we changed it. > > We have tightened behaviour in ways much harder to fix in the past, e.g. actually following UTF8 rules. Fixing breakage in this case would be pretty trivial, and nobody has any real right to expect the current behaviour to work. But I won't be surprised to be in a minority on this .... cheers andrew
""Albe Laurenz"" <all@adv.magwien.gv.at> > Tom Lane wrote: ... > > If modifying the loop variable is disallowed in PL/SQL, I'm all for > > disallowing it in plpgsql, otherwise not. Anyone have a > > recent copy of Oracle to try it on? > > I tried this on Oracle 10.2.0.2.0 (which is the most recent version): > > SET SERVEROUTPUT ON > BEGIN > FOR i IN 1..10 LOOP > i := i + 1; > DBMS_OUTPUT.PUT_LINE(i); > END LOOP; > END; > / > i := i + 1; > * > ERROR at line 3: > ORA-06550: line 3, column 7: > PLS-00363: expression 'I' cannot be used as an assignment target > ORA-06550: line 3, column 7: > PL/SQL: Statement ignored > > And the documentation also explicitly states that it is not allowed. > > By the way, PL/SQL screams if you want to do an assignment with '='. > But I guess that the current behaviour of PL/pgSQL should not reflect > that to maintain backward compatibility, right? > I think Oracle's syntax and behaviour are better. As for this feature, breaking the backward compatibility is acceptable. Regards, William ZHANG
Andrew Dunstan <andrew@dunslane.net> writes:
> Tom Lane wrote:
>> The lack of documentation is easily fixed ;-). I don't think this is
>> icky enough to justify breaking all the existing functions we'd
>> undoubtedly break if we changed it.
> We have tightened behaviour in ways much harder to fix in the past, e.g.
> actually following UTF8 rules.
True, but there were clear benefits from doing so. Disallowing "="
assignment in plpgsql wouldn't buy anything, just break programs.
regards, tom lane
On Tue, May 16, 2006 at 07:56:25PM -0700, David Wheeler wrote: > On May 16, 2006, at 19:52, Tom Lane wrote: > > >Distant ancestors aren't particularly relevant here. What plpgsql > >tries > >to be is a ripoff^H^H^H^H^H^Hsincere flattery of Oracle's PL/SQL. If > >modifying the loop variable is disallowed in PL/SQL, I'm all for > >disallowing it in plpgsql, otherwise not. > > Even if PL/SQL disallows it, why would you not allow it in PL/pgSQL? > So that it's easier to migrate from PostgreSQL to Oracle? > > If you only care about Oracle to PostgreSQL (and who wouldn't?), then > it in fact seems desirable for PL/pgSQL to be a superset of PL/SQL. Well, I'd argue that if we were serious about the migration case we'd just add PL/SQL as a language. Presumably EnterpriseDB has done that, and might be willing to donate that to the community. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Tue, May 16, 2006 at 07:56:25PM -0700, David Wheeler wrote: > If you only care about Oracle to PostgreSQL (and who wouldn't?), then > it in fact seems desirable for PL/pgSQL to be a superset of PL/SQL. Oh, and PL/SQL is a lot more powerful than plpgsql. See packages for one thing... -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Wed, May 17, 2006 at 10:11:39AM -0400, Tom Lane wrote: > The lack of documentation is easily fixed ;-). I don't think this is > icky enough to justify breaking all the existing functions we'd > undoubtedly break if we changed it. I thought the suggestion was to complain loudly (presumably during CREATE FUNCTION), but not throw an error. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Tom, > If you only care about Oracle to PostgreSQL (and who wouldn't?), then > it in fact seems desirable for PL/pgSQL to be a superset of PL/SQL. I agree with David here. We care about the ability to migrate PL/SQL --> PL/pgSQL. We do *not* care about the ability to migrate PL/pgSQL --> PL/SQL. So supporting extra syntax which Oracle doesn't ... as long as the Oracle syntax still works ... is in fact a good thing. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco
Tom, > True, but there were clear benefits from doing so. Disallowing "=" > assignment in plpgsql wouldn't buy anything, just break programs. But it's already disallowed in most places. The i = i + 1 seems to be an exception. So what happens to "i" if I do: IF i = i + 1 THEN .... does "i" increment? If so, isn't that a bug? I don't think too many people are using that functionality intentionally; I probably write more PL/pgSQL than anyone and would regard any assignment without ":=" as a bug. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco
Josh Berkus <josh@agliodbs.com> writes:
>> True, but there were clear benefits from doing so. Disallowing "="
>> assignment in plpgsql wouldn't buy anything, just break programs.
> But it's already disallowed in most places.
No it isn't. The plpgsql scanner treats := and = as *the same token*.
They can be interchanged freely. This has nothing to do with the case
of modifying a loop variable in particular.
regards, tom lane
Ühel kenal päeval, K, 2006-05-17 kell 10:22, kirjutas Josh Berkus: > Tom, > > > True, but there were clear benefits from doing so. Disallowing "=" > > assignment in plpgsql wouldn't buy anything, just break programs. > > But it's already disallowed in most places. The i = i + 1 seems to be an > exception. > > So what happens to "i" if I do: > > IF i = i + 1 THEN .... > > does "i" increment? If so, isn't that a bug? > > I don't think too many people are using that functionality intentionally; I > probably write more PL/pgSQL than anyone and would regard any assignment > without ":=" as a bug. I do occasionally write some pl/pgSQL, and have at some points written a lot of it. And most of it uses = instead of := , including all code written during last 1.5 years. Once I found out that = works for assignment, i completely stopped using := .I have treated := as "deprecated" for some time already. So allowing only := for assignment would make me very sad . -- ---------------- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com
On Wed, May 17, 2006 at 10:18:51AM -0700, Josh Berkus wrote: > Tom, > > > If you only care about Oracle to PostgreSQL (and who wouldn't?), then ? > > it in fact seems desirable for PL/pgSQL to be a superset of PL/SQL. > > I agree with David here. We care about the ability to migrate PL/SQL --> > PL/pgSQL. We do *not* care about the ability to migrate PL/pgSQL --> > PL/SQL. So supporting extra syntax which Oracle doesn't ... as long as > the Oracle syntax still works ... is in fact a good thing. Except someone did make a very good point that as soon as we add some feature that Oracle doesn't have, they can turn around and add the same feature using a different syntax, and then we'd have a real mess. If we're going to be serious about easing migration from Oracle we should really be adding PL/SQL as a language, because there's already some pretty non-trivial differences (off the top of my head, how you handle sending debug info back is a big one, as is our lack of packages). IMO, if the community is going to concentrate on a migration path, I think MySQL is a much better target: - There's already a commercial solution for migrating from Oracle, and there's probably more money there than in migratingfrom MySQL - Enabling migration from MySQL would be a tremendous benefit to the growth of the community, because there's a lot morepeople who would try that on a whim and stick with PostgreSQL than for any of the commercial RDBMSes - Having some kind of compatability mode would make it much easier on all the OSS projects that currently only support MySQLto add PostgreSQL support. It also makes it much easier for people to use PostgreSQL over MySQL -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Hannu Krosing wrote: >> I don't think too many people are using that functionality intentionally; I >> probably write more PL/pgSQL than anyone and would regard any assignment >> without ":=" as a bug. >> > > I do occasionally write some pl/pgSQL, and have at some points written a > lot of it. And most of it uses = instead of := , including all code > written during last 1.5 years. > > Once I found out that = works for assignment, i completely stopped > using := .I have treated := as "deprecated" for some time already. > > So allowing only := for assignment would make me very sad . > I can only comment that that seems crazy. := is . documented . consistent with pl/sql and ancestors Even C doesn't use the same operator for assignment and comparison. Sometimes I wonder if large parts of the IT world is trying to pretend that the Algol family never existed. cheers andrew
Ühel kenal päeval, K, 2006-05-17 kell 16:31, kirjutas Andrew Dunstan: > Hannu Krosing wrote: > >> I don't think too many people are using that functionality intentionally; I > >> probably write more PL/pgSQL than anyone and would regard any assignment > >> without ":=" as a bug. > >> > > > > I do occasionally write some pl/pgSQL, and have at some points written a > > lot of it. And most of it uses = instead of := , including all code > > written during last 1.5 years. > > > > Once I found out that = works for assignment, i completely stopped > > using := .I have treated := as "deprecated" for some time already. > > > > So allowing only := for assignment would make me very sad . > > > > I can only comment that that seems crazy. > > := is > . documented > . consistent with pl/sql and ancestors OTOH * = is used in CONST declaration in PASCAL, whereas pl/pgSQL documents := (i.e. ASSIGNMENT) for const declaration * = is used by everybody else for assignment. It can be argued that the reason it is not used in pure SQL for assignmentis just that SQL lacks assignment. * weather = is assignment statement or comparison operator is always clear from position, that is pl/pgsql does not haveresult for assignment statement, so everywhere the value of "A = B" is used, it must be comparison operator, whereaswhen its value is not used it must be statement. So no disambiguity. > Even C doesn't use the same operator for assignment and comparison. It can't, as in C both assignment and comparison are operators, so it allows you to use result of both as boolean. > Sometimes I wonder if large parts of the IT world is trying to pretend > that the Algol family never existed. And even bigger part is trying to pretend that LISP and Prolog never existed ;) And don't forget about QUEL and PostQUEL either. -- ---------------- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com
Hannu Krosing said: > > * = is used in CONST declaration in PASCAL, whereas pl/pgSQL > documents := (i.e. ASSIGNMENT) for const declaration Pascal is not the direct ancestor of PL/SQL, Ada is, and it uses := for constant initialisation. cheers andrew
On 5/17/06, Andrew Dunstan <andrew@dunslane.net> wrote: > Pascal is not the direct ancestor of PL/SQL, Ada is, and it uses := for > constant initialisation. True dat. Almost all PL/SQL components come from ADA. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/
Ühel kenal päeval, K, 2006-05-17 kell 17:51, kirjutas Jonah H. Harris: > On 5/17/06, Andrew Dunstan <andrew@dunslane.net> wrote: > > Pascal is not the direct ancestor of PL/SQL, Ada is, and it uses := for > > constant initialisation. Does ADA have both assignment and comparison as operators, or is assignment a statement ? > True dat. Almost all PL/SQL components come from ADA. Maybe we need just pl/ADA ;) ? -- ---------------- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com
On 5/17/06, Hannu Krosing <hannu@skype.net> wrote: > Does ADA have both assignment and comparison as operators, or is > assignment a statement ? Yes. Assignment is := and comparison is = > Maybe we need just pl/ADA ;) ? Wouldn't hurt :) -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/
Andrew Dunstan <andrew@dunslane.net> writes:
> Even C doesn't use the same operator for assignment and comparison.
However, SQL *does*, so it seems to me that plsql is gratuitously
inconsistent with its larger environment.
regards, tom lane
On 5/17/06, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Jaime Casanova" <systemguards@gmail.com> writes: > >> (No, I'm not particularly in favor of the BY feature mentioned upthread, > >> either.) > > > mmm... and why is that? > > Essentially because it's not in the upstream language. Oracle could > come out with the same feature next week, only they use STEP or some > other syntax for it, and then we'd have a mess on our hands. If the > feature were sufficiently compelling use-wise then I'd be willing to > risk that, but it doesn't seem to me to be more than a marginal > notational improvement. > > regards, tom lane > good point... just one comment, if you disallow the ability to modify the loop variable the BY clause won't be a "notational" improvement anymore (but it still will be a marginal one, must admit)... so i think that the painless path is to do nothing at all... no BY clause, no disallow the ability to modify the loop variable... -- regards, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook
Ühel kenal päeval, K, 2006-05-17 kell 20:17, kirjutas Jonah H. Harris: > On 5/17/06, Hannu Krosing <hannu@skype.net> wrote: > > Does ADA have both assignment and comparison as operators, or is > > assignment a statement ? > > Yes. Assignment is := and comparison is = I meant to ask, if := is a statement and = is an operator in ADA or are both operators. Statemants and operators are two different language constructs, usable in different places. In C both = and == are operators and results of both can further be used in expressions, in most languages assignment is a statement not an operator, and statements have no result. Can you do something like "A > (B := C)" or "IF (A:=B) THEN ..." in ADA ? -- ---------------- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com NOTICE: This communication contains privileged or other confidential information. If you have received it in error, please advise the sender by reply email and immediately delete the message and any attachments without copying or disclosing the contents.
Josh Berkus wrote: >> If you only care about Oracle to PostgreSQL (and who wouldn't?), then >> it in fact seems desirable for PL/pgSQL to be a superset of PL/SQL. > > I agree with David here. We care about the ability to migrate PL/SQL --> > PL/pgSQL. We do *not* care about the ability to migrate PL/pgSQL --> > PL/SQL. So supporting extra syntax which Oracle doesn't ... as long as > the Oracle syntax still works ... is in fact a good thing. I cannot feel good about deliberately introducing incompatibilities. We are not Microsoft, are we? If assignment by '=' gets documented and known, it will get used. This in turn will make PL/pgSQL less familiar for PL/SQL coders. And that will make them more reluctant to change over. I think it would be best to get a compile error when '=' is used for assignment, but if that's too much effort, I believe that the current behaviour is acceptable as long as it doesn't get documented and 'good practice'. Yours, Laurenz Albe
* Jonah H. Harris: > On 5/17/06, Hannu Krosing <hannu@skype.net> wrote: >> Does ADA have both assignment and comparison as operators, or is >> assignment a statement ? > > Yes. Assignment is := and comparison is = And its name is spelled "Ada", not "ADA", even though the language itself is case-insensitive.
Ühel kenal päeval, N, 2006-05-18 kell 09:28, kirjutas Albe Laurenz: > Josh Berkus wrote: > >> If you only care about Oracle to PostgreSQL (and who wouldn't?), then > >> it in fact seems desirable for PL/pgSQL to be a superset of PL/SQL. > > > > I agree with David here. We care about the ability to migrate PL/SQL --> > > PL/pgSQL. We do *not* care about the ability to migrate PL/pgSQL --> > > PL/SQL. So supporting extra syntax which Oracle doesn't ... as long as > > the Oracle syntax still works ... is in fact a good thing. > > I cannot feel good about deliberately introducing incompatibilities. I guess that PL/pgSQL will always be "an extended subset" of PL/SQL, and never be direct replacement, so I dont see another extension as introducing incompatibilities. > If assignment by '=' gets documented and known, it will get used. > This in turn will make PL/pgSQL less familiar for PL/SQL coders. > And that will make them more reluctant to change over. Someone else using = instead of := is the least of their worries when switching to PostgreSQL (and you cant switch to PL/pgSQL without switching to PostgreSQL). PostgreSQL generally behaves differently on much deeper levels, sometimes better sometimes worse. And you need to optimise code in different ways, as much of oracles deep secrets are not applicable to pg. > I think it would be best to get a compile error when '=' is used for > assignment, but if that's too much effort, I believe that the current > behaviour is acceptable as long as it doesn't get documented and > 'good practice'. What does PL/SQL use for assignment inside UPDATE statements ? Is it "SET A=B" like in SQL or "SET A:=B" like in rest of PL/SQL ? -- ---------------- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com
Hannu Krosing wrote: > > Can you do something like "A > (B := C)" or "IF (A:=B) THEN ..." in > ADA ? > > Er, that's "Ada"; "ADA" is the American Dental Association among other things. And no you certainly can't do it. Assignment is a statement, not an expression, and this family of languages distinguishes between the two quite sharply. This is quite different from, say, C, where an assignment statement is simply an expression whose evaluation has a side effect and whose value is thrown away. Anyway, this discussion seems to going nowhere much. cheers andrew
Tom Lane wrote: > Josh Berkus <josh@agliodbs.com> writes: > >>>True, but there were clear benefits from doing so. Disallowing "=" >>>assignment in plpgsql wouldn't buy anything, just break programs. > > >>But it's already disallowed in most places. > > > No it isn't. The plpgsql scanner treats := and = as *the same token*. > They can be interchanged freely. This has nothing to do with the case > of modifying a loop variable in particular. I disagree. If the scanner treated them the same, then if i := 1 then ... would work, but it doesn't. The := is rejected in a conditional. Try the following code if you don't believe me: CREATE OR REPLACE FUNCTION foo () RETURNS INTEGER AS $$ DECLARE i integer; BEGIN i := 1; if i := 1 then return 1; end if; return 0; END; $$ LANGUAGE plpgsql;
Mark Dilger schrob: > Tom Lane wrote: >> No it isn't. The plpgsql scanner treats := and = as *the same token*. >> They can be interchanged freely. This has nothing to do with the case >> of modifying a loop variable in particular. > > I disagree. If the scanner treated them the same, then > > if i := 1 then ... > > would work, but it doesn't. The := is rejected in a conditional. Try the > following code if you don't believe me: You're confusing the PL/pgSQL scanner with the SQL scanner. Expressions in PL/pgSQL are handed down to the SQL parser. regards, andreas
Mark Dilger <pgsql@markdilger.com> writes: > Tom Lane wrote: >> No it isn't. The plpgsql scanner treats := and = as *the same token*. >> They can be interchanged freely. This has nothing to do with the case >> of modifying a loop variable in particular. > > I disagree. If the scanner treated them the same, then > > if i := 1 then ... > > would work, but it doesn't. The := is rejected in a conditional. Try the > following code if you don't believe me: That's because (AIUI) all expressions to be evaluated are handed off to the SQL parser (why re-implement all that logic and have subtle and annoying differences?) plpgsql only handles the statements, loops, etc. So it doesn't care about the difference but SQL does... Not that I claim that makes sense. :) -Doug
Douglas McNaught wrote: > Mark Dilger <pgsql@markdilger.com> writes: > > >>Tom Lane wrote: >> >>>No it isn't. The plpgsql scanner treats := and = as *the same token*. >>>They can be interchanged freely. This has nothing to do with the case >>>of modifying a loop variable in particular. >> >>I disagree. If the scanner treated them the same, then >> >> if i := 1 then ... >> >>would work, but it doesn't. The := is rejected in a conditional. Try the >>following code if you don't believe me: > > > That's because (AIUI) all expressions to be evaluated are handed off > to the SQL parser (why re-implement all that logic and have subtle and > annoying differences?) plpgsql only handles the statements, loops, etc. > So it doesn't care about the difference but SQL does... Ok, ten out of ten for technical accuracy; the error occurs at a lower level. But that really doesn't matter, does it? If the syntax results in an error, then the argument that '=' and ':=' are interchangeable is wrong. As a coder, if you notice that using ':=' within a conditional fails, wouldn't you think that implied that ':=' is for assignment and '=' is for comparison? mark
On Thu, May 18, 2006 at 08:40:04PM -0400, Douglas McNaught wrote: > Mark Dilger <pgsql@markdilger.com> writes: > > > Tom Lane wrote: > >> No it isn't. The plpgsql scanner treats := and = as *the same token*. > >> They can be interchanged freely. This has nothing to do with the case > >> of modifying a loop variable in particular. > > > > I disagree. If the scanner treated them the same, then > > > > if i := 1 then ... > > > > would work, but it doesn't. The := is rejected in a conditional. Try the > > following code if you don't believe me: > > That's because (AIUI) all expressions to be evaluated are handed off > to the SQL parser (why re-implement all that logic and have subtle and > annoying differences?) plpgsql only handles the statements, loops, etc. > So it doesn't care about the difference but SQL does... Something that's always bugged me is how horribly variables are handled in plpgsql, namely that if you have a variable and a field with the same name it can be extremely difficult to keep them seperated. Perhaps := vs = might be a way to keep it clear as to which is which... Though, a better way would probably just be to provide a built-in construct for referencing plpgsql variables, such as $. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Ühel kenal päeval, R, 2006-05-19 kell 11:24, kirjutas Jim C. Nasby: > Something that's always bugged me is how horribly variables are handled > in plpgsql, namely that if you have a variable and a field with the same > name it can be extremely difficult to keep them seperated. Perhaps := vs > = might be a way to keep it clear as to which is which... I can't see how := helps here. Once you have fields, you are either in SQL-land and always use = or have the fields selected into a type or recors and can use record.field syntax. > Though, a better way would probably just be to provide a built-in > construct for referencing plpgsql variables, such as $. Where is it exactly a problem which can't be solved simply by naming your variables differently from fields? -- ---------------- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com NOTICE: This communication contains privileged or other confidential information. If you have received it in error, please advise the sender by reply email and immediately delete the message and any attachments without copying or disclosing the contents.
On Fri, May 19, 2006 at 09:57:29PM +0300, Hannu Krosing wrote: > ??hel kenal p??eval, R, 2006-05-19 kell 11:24, kirjutas Jim C. Nasby: > > > Something that's always bugged me is how horribly variables are handled > > in plpgsql, namely that if you have a variable and a field with the same > > name it can be extremely difficult to keep them seperated. Perhaps := vs > > = might be a way to keep it clear as to which is which... > > I can't see how := helps here. Once you have fields, you are either in > SQL-land and always use = or have the fields selected into a type or > recors and can use record.field syntax. The problem happens down in the SQL layer. Actually, I guess := wouldn't help anything... > > Though, a better way would probably just be to provide a built-in > > construct for referencing plpgsql variables, such as $. > > Where is it exactly a problem which can't be solved simply by naming > your variables differently from fields? That's how I solve it, but a lot of newbies get bit by this. What makes it really bad is that they typically get bit after they've already written a bunch of code that doesn't prefix variable names with something, so then they have to switch coding-conventions after they already have a bunch of code written. I think it would be better to at least strongly recommend always prefixing variables and parameters with something. I'd argue that it'd be even better to put us on the road of eventually mandating plpgsql variables be prefixed with something (like $), but I'm not holding my breath on that one... -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Ühel kenal päeval, R, 2006-05-19 kell 14:39, kirjutas Jim C. Nasby: > On Fri, May 19, 2006 at 09:57:29PM +0300, Hannu Krosing wrote: > > ??hel kenal p??eval, R, 2006-05-19 kell 11:24, kirjutas Jim C. Nasby: > > > > > Something that's always bugged me is how horribly variables are handled > > > in plpgsql, namely that if you have a variable and a field with the same > > > name it can be extremely difficult to keep them seperated. Perhaps := vs > > > = might be a way to keep it clear as to which is which... > > > > I can't see how := helps here. Once you have fields, you are either in > > SQL-land and always use = or have the fields selected into a type or > > recors and can use record.field syntax. > > The problem happens down in the SQL layer. Actually, I guess := wouldn't > help anything... > > > > Though, a better way would probably just be to provide a built-in > > > construct for referencing plpgsql variables, such as $. > > > > Where is it exactly a problem which can't be solved simply by naming > > your variables differently from fields? > > That's how I solve it, but a lot of newbies get bit by this. A newbie will be bit by several things anyway. For example you could write code in C (and several other languages) with all your loop variables named "i", and those in inner scopes overshadowing those in outer. > What makes > it really bad is that they typically get bit after they've already > written a bunch of code that doesn't prefix variable names with > something, so then they have to switch coding-conventions after they > already have a bunch of code written. A less disruptive change would be to have some predefined "record" where all local variables belong to, perhaps called 'local' or '_local_' :) so one could access both input parameter inp_orderdate and declared variable var_orderdate as local.inp_orderdate and local.var_orderdate respectively ? The old use (without local.) should still work. > I think it would be better to at least strongly recommend always > prefixing variables and parameters with something. At least we should use such convention in our sample code in docs. > I'd argue that it'd > be even better to put us on the road of eventually mandating plpgsql > variables be prefixed with something (like $), but I'm not holding my > breath on that one... I dont believe in mandating non-backward-compatible changes, but prefix $ may be one way to disambiguate vars and fieldnames. -- ---------------- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com
On Fri, May 19, 2006 at 11:03:21PM +0300, Hannu Krosing wrote: > A less disruptive change would be to have some predefined "record" where > all local variables belong to, perhaps called 'local' or '_local_' :) so > one could access both input parameter inp_orderdate and declared > variable var_orderdate as local.inp_orderdate and local.var_orderdate > respectively ? The old use (without local.) should still work. That might be useful for othing things, too. Having _local. and _parameters. could be handy, for example. But I'm not sure if this is better than using $ or not... IIRC, Oracle handles this by allowing you to prefix variables with the name of the function. You can also have package-level variables, which can be handy (whatever happened to the discussion about adding packages or something similar to plpgsql??) > > I think it would be better to at least strongly recommend always > > prefixing variables and parameters with something. > > At least we should use such convention in our sample code in docs. Yes, at a minimum. > > I'd argue that it'd > > be even better to put us on the road of eventually mandating plpgsql > > variables be prefixed with something (like $), but I'm not holding my > > breath on that one... > > I dont believe in mandating non-backward-compatible changes, but prefix > $ may be one way to disambiguate vars and fieldnames. Well, this could be made optional (strict mode). -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Ühel kenal päeval, R, 2006-05-19 kell 16:12, kirjutas Jim C. Nasby: > On Fri, May 19, 2006 at 11:03:21PM +0300, Hannu Krosing wrote: > > A less disruptive change would be to have some predefined "record" where > > all local variables belong to, perhaps called 'local' or '_local_' :) so > > one could access both input parameter inp_orderdate and declared > > variable var_orderdate as local.inp_orderdate and local.var_orderdate > > respectively ? The old use (without local.) should still work. > > That might be useful for othing things, too. Having _local. and > _parameters. could be handy, for example. But I'm not sure if this is > better than using $ or not... I dont think that having separate _parameters and _locals is a good idea (then we would probably also need _const :) ). lerts have just _vars OR _locals. > IIRC, Oracle handles this by allowing you to prefix variables with the > name of the function. what happens if your function name is the same as some table name or local record variable name ? would it still bite newcomers ? > You can also have package-level variables, which > can be handy (whatever happened to the discussion about adding packages > or something similar to plpgsql??) I got the impression that this was either a) tied together with adding *procedures* in addition to functions or b) planned somehow to be solved by using schemas > > > I think it would be better to at least strongly recommend always > > > prefixing variables and parameters with something. > > > > At least we should use such convention in our sample code in docs. > > Yes, at a minimum. > > > > I'd argue that it'd > > > be even better to put us on the road of eventually mandating plpgsql > > > variables be prefixed with something (like $), but I'm not holding my > > > breath on that one... > > > > I dont believe in mandating non-backward-compatible changes, but prefix > > $ may be one way to disambiguate vars and fieldnames. > > Well, this could be made optional (strict mode). or perhaps have plpgsql_lint as a separate external tool or as a database function :) I guess it is hard to make a strict mode, when the need for using $ in first place comes from inability to distinguish between fields and variables :) -- ---------------- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com NOTICE: This communication contains privileged or other confidential information. If you have received it in error, please advise the sender by reply email and immediately delete the message and any attachments without copying or disclosing the contents.
Hannu Krosing <hannu@skype.net> writes:
> Ühel kenal päeval, R, 2006-05-19 kell 16:12, kirjutas Jim C. Nasby:
>> IIRC, Oracle handles this by allowing you to prefix variables with the
>> name of the function.
> what happens if your function name is the same as some table name or
> local record variable name ? would it still bite newcomers ?
Yeah. Since functions and tables have independent namespaces in PG
(dunno about Oracle), this didn't seem like much of a solution to me.
I think we should just recommend a coding practice such as _ prefix
for local variables, and leave it at that.
regards, tom lane