PostgreSQL function with a loop

Question

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

Is that one sum per value of "*round*"? Can you show us the expected output for your sample data? — , Jan 18 '12 at 23:25
@a_horse_with_no_name Hi, please look at the Update section :D thank you — BPm, Jan 18 '12 at 23:49
tl;dr but from the looks of it, you might be looking for PostgreSQL's window functions. See http://www.postgresql.org/docs/current/static/tutorial-window.html for more info. — Kenaniah, Jan 19 '12 at 00:03

Erwin Brandstetter · Accepted Answer · 2022-01-30T04:30:03.340

@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

score 5 · Answer 2 · edited May 23 '17 at 11:58

I guess you are looking for an aggregate "product" function. You can create your own aggregate functions in Postgresql and Oracle.

    CREATE TABLE mytable(name varchar(32), round int, position int, val decimal);

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

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

    CREATE AGGREGATE product(double precision) (SFUNC=float8mul, STYPE=double precision, INITCOND=1);

    SELECT name, round, product(1-val/100) AS result
      FROM mytable
      GROUP BY name, round;

     name | round |     result
    ------+-------+----------------
     A    |     2 | 0.653458283632
     A    |     1 |  0.90430333812
    (2 rows)

See "User-Defined Aggregates" in the Postgresql doc. The example above I borrowed from here. There are other stackoverflow responses that show other methods to do this.

PostgreSQL function with a loop

2 Answers2

Generic function

Specific function as per question