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
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.
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.
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.
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';
@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)
;
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With