Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL function with a loop

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
like image 702
BPm Avatar asked Jan 18 '12 22:01

BPm


People also ask

Can we use loop in PostgreSQL?

Tables in PostgreSQL can be used to store data in various ways. We utilize several looping and conditional expressions to retrieve data and perform multiple operations. We require a looping technique to iterate through the stored data. Many conditional and looping commands are available in the PostgreSQL database.

How do I create a loop in PostgreSQL?

The syntax of the for loop statement to iterate over a range of integers: [ <<label>> ] for loop_cnt in [ reverse ] from.. to [ by step ] loop statements end loop [ label ]; If we analyse the above syntax: An integer variable loop_cnt is created at first, which is accessible inside the loop only.

Do while loops PostgreSQL?

The PostgreSQL WHILE LOOP evaluates the condition defined to decide whether the loop should be terminated or continued for execution. If the condition defined with PostgreSQL WHILE LOOP evaluates to true, then the body of WHILE LOOP or code statements are written inside the PostgreSQL WHILE LOOP is executed.

What is do $$ in PostgreSQL?

In PostgreSQL, the dollar-quoted string constants ($$) is used in user-defined functions and stored procedures. In PostgreSQL, you use single quotes for a string constant like this: select 'String constant';


1 Answers

@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
like image 127
Erwin Brandstetter Avatar answered Oct 07 '22 19:10

Erwin Brandstetter