Thread: hi, what is wrong with my newbie sql?

Hi, the following sql returns "10" and not "20" as would be expected.

Where is the error? This would for sure work in any programming
language, why is this different?

CREATE FUNCTION test () RETURNS INTEGER AS '

    DECLARE
        k integer;

    BEGIN
        k = 10;
        FOR k IN 1..10 LOOP
            k = k +1;
        END LOOP;

        return k;
    END;

' LANGUAGE 'plpgsql';

Ronin <jkoorts@gmail.com> schrieb:

> Hi, the following sql returns "10" and not "20" as would be expected.
>
> Where is the error? This would for sure work in any programming
> language, why is this different?
>
> CREATE FUNCTION test () RETURNS INTEGER AS '
>
>     DECLARE
>         k integer;
>
>     BEGIN
>         k = 10;
>         FOR k IN 1..10 LOOP
>             k = k +1;
>         END LOOP;
>
>         return k;
>     END;

I think, the inner k = k + 1; is not visible outer. The loop runs from
k=1 to k=10, and the returning k is the last loop-value k, 10.

You need a extra variable for the loop-counter.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

"Ronin" <jkoorts@gmail.com> writes:
>         k = 10;
>         FOR k IN 1..10 LOOP
>             k = k +1;
>         END LOOP;

>         return k;

An integer for-loop implicitly declares its control variable, so the "k"
inside the loop is unrelated to the "k" outside.  Hence you get 10.

As for what you'd get if they were the same variable, I dunno why you're
so certain it would be 20.  Munging a loop's control variable by hand is
a good way to trip over undocumented details of the loop implementation.

            regards, tom lane