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

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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...4/msg00138.php

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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...4/msg00138.php

Ah, cool, then it *is* known.

Thanks,

David

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq