[SOLVED] PostgreSQL function with a loop

Issue

I’m not good at postgres functions. Could you help me out?
Say, I have this db:

name    | round   |position | val
-----------------------------------
A       | 1       | 1       | 0.5
A       | 1       | 2       | 3.4
A       | 1       | 3       | 2.2
A       | 1       | 4       | 3.8
A       | 2       | 1       | 0.5
A       | 2       | 2       | 32.3
A       | 2       | 3       | 2.21
A       | 2       | 4       | 0.8

I want to write a Postgres function that can loop from position=1 to position=4 and calculate the corresponding value. I could do this in python with psycopg2:

import psycopg2
import psycopg2.extras

conn = psycopg2.connect("host='localhost' dbname='mydb' user='user' password='pass'")
CURSOR = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cmd = """SELECT name, round, position, val from mytable"""
CURSOR.execute(cmd)
rows = CURSOR.fetchall()

dict = {}
for row in rows:
    indx = row['round']
    try:
        dict[indx] *= (1-row['val']/100)
    except:
        dict[indx] = (1-row['val']/100)
    if row['position'] == 4:
        if indx == 1:
            result1 = dict[indx]
        elif indx == 2:
            result2 = dict[indx]
print result1, result2

How can I do the same thing directly in Postgres so that it returns a table of (name, result1, result2)

UPDATE:
@a_horse_with_no_name, the expected value would be:

result1 = (1 - 0.5/100) * (1 - 3.4/100) * (1 - 2.2/100) * (1 - 3.8/100) = 0.9043
result2 = (1 - 0.5/100) * (1 - 32.3/100) * (1 - 2.21/100) * (1 - 0.8/100) = 0.6535

Solution

@Glenn gave you a very elegant solution with an aggregate function. But to answer your question, a plpgsql function could look like this:

Test setup:

CREATE TEMP TABLE mytable (
  name  text
, round int
, position int
, val double precision
);

INSERT INTO mytable VALUES
  ('A', 1, 1, 0.5)
, ('A', 1, 2, 3.4)
, ('A', 1, 3, 2.2)
, ('A', 1, 4, 3.8)
, ('A', 2, 1, 0.5)
, ('A', 2, 2, 32.3)
, ('A', 2, 3, 2.21)
, ('A', 2, 4, 0.8)
;

Generic function

CREATE OR REPLACE FUNCTION f_grp_prod()
  RETURNS TABLE (name text
               , round int
               , result double precision)
  LANGUAGE plpgsql STABLE AS
$func$
DECLARE
   r mytable%ROWTYPE;
BEGIN
   -- init vars
   name   := 'A';  -- we happen to know initial value
   round  := 1;    -- we happen to know initial value
   result := 1;

   FOR r IN
      SELECT *
      FROM   mytable m
      ORDER  BY m.name, m.round
   LOOP
      IF (r.name, r.round) <> (name, round) THEN   -- return result before round
         RETURN NEXT;
         name   := r.name;
         round  := r.round;
         result := 1;
      END IF;

      result := result * (1 - r.val/100);
   END LOOP;

   RETURN NEXT;   -- return final result
END
$func$;

Call:

SELECT * FROM f_grp_prod();

Result:

name | round |  result
-----+-------+---------------
A    | 1     | 0.90430333812
A    | 2     | 0.653458283632

Specific function as per question

CREATE OR REPLACE FUNCTION f_grp_prod(text)
  RETURNS TABLE (name text
               , result1 double precision
               , result2 double precision)
  LANGUAGE plpgsql STABLE AS
$func$
DECLARE
   r      mytable%ROWTYPE;
   _round integer;
BEGIN
   -- init vars
   name    := $1;
   result2 := 1;      -- abuse result2 as temp var for convenience

   FOR r IN
      SELECT *
      FROM   mytable m
      WHERE  m.name = name
      ORDER  BY m.round
   LOOP
      IF r.round <> _round THEN   -- save result1 before 2nd round
         result1 := result2;
         result2 := 1;
      END IF;

      result2 := result2 * (1 - r.val/100);
      _round  := r.round;
   END LOOP;

   RETURN NEXT;
END
$func$;

Call:

SELECT * FROM f_grp_prod('A');

Result:

name | result1       |  result2
-----+---------------+---------------
A    | 0.90430333812 | 0.653458283632

Answered By – Erwin Brandstetter

Answer Checked By – Senaida (BugsFixing Volunteer)