PL/pgSQL 'i = i + 1' Syntax

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;
END LOOP;

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 migrating from MySQL

- Enabling migration from MySQL would be a tremendous benefit to the
growth of the community, because there's a lot more people 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 MySQL to 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

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

Ü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 assignment is just that SQL
lacks assignment.
* weather = is assignment statement or comparison operator is always
clear from position, that is pl/pgsql does not have result for
assignment statement, so everywhere the value of "A = B" is used,
it must be comparison operator, whereas when 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

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

* 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, 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

Ü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