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