CODEWITHSUNDEEP
postgresqlloopsfor-loop
Parvesh
Parvesh

Reputation: 439

Postgresql - Basic loop

Have to write a function which returns the sum of all the square between 2 user input numbers. Here is what i have written but cant seem to make it work. E.g. sumAll(2, 5) must give the result 54 . 

CREATE OR REPLACE FUNCTION SumAll(integer,integer) RETURNS integer as $$
DECLARE
    num1 ALIAS for $1;
    num2 ALIAS for $2;
    ret_sum integer;
    sum1 integer;
BEGIN

    for i in num1..num2 LOOP
        sum1:=i*i;
        ret_sum=ret_sum+sum1;
    END LOOP;
    return ret_sum;
END
$$ language 'plpgsql';

it doesnt work. what am i doing wrong?

Upvotes: 0

Views: 6202

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658032

@pukipuki pointed out your mistake. I would generally simplify the syntax, though:

CREATE OR REPLACE FUNCTION sumall(_num1 int, _num2 int, OUT ret_sum int)
  RETURNS int AS
$BODY$
BEGIN
    ret_sum := 0;

    FOR i IN _num1 .. _num2 LOOP
      ret_sum := ret_sum + i*i;
    END LOOP;
END
$BODY$ LANGUAGE plpgsql;

Upvotes: 1

araqnid
araqnid

Reputation: 133692

Writing a procedure to iterate over the numbers suggests you haven't grokked how to approach working with sets of data in SQL. Things like summing a set of numbers shouldn't require writing a loop manually (and writing this in plpgsql isn't playing to its strengths).

You can produce the set of numbers in the range using the helper function generate_series, and square them to produce all the values to sum up:

steve@steve@[local] =# SELECT x, x*x FROM generate_series(2, 5) x;
 x | ?column? 
---+----------
 2 |        4
 3 |        9
 4 |       16
 5 |       25
(4 rows)

Then just use the SUM() aggregate function:

steve@steve@[local] =# SELECT sum(x*x) FROM generate_series(2, 5) x;
 sum 
-----
  54
(1 row)

Upvotes: 1

Timur Sadykov
Timur Sadykov

Reputation: 11406

You've forgot to initialize one variable, add ret_sum:=0; before for-loop.

Upvotes: 1

Related Questions