Postgresql - Basic loop

Question

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?

Uhhh.... What is the purpose of this? Specifically, why do this in postgresql? — Corbin
– Corbin, Commented Mar 13, 2012 at 3:30
Well i just got started with postgresql.Going through some tutorials online. Trying to complete it. — Parvesh
– Parvesh, Commented Mar 13, 2012 at 3:33

Timur Sadykov · Accepted Answer · 2012-03-13 03:36:44Z

1

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

answered Mar 13, 2012 at 3:36

Timur Sadykov

11.6k7 gold badges35 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Parvesh Over a year ago

Excellent. it works for me. forgot to initialize the variable.

araqnid · Accepted Answer · 2012-03-13 11:55:59Z

1

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)

answered Mar 13, 2012 at 11:55

araqnid

135k25 gold badges163 silver badges138 bronze badges

Comments

Erwin Brandstetter · Accepted Answer · 2012-03-13 12:51:38Z

1

@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;

answered Mar 13, 2012 at 12:51

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

Postgresql - Basic loop

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related