I´m making some scripts for a affiliate-program for my site and I´m having a problem with with the script that´s supposed to update the points every night. The reason why I need this script is because the program is multi-level, so if a user has made som points during the day his referrers should also be rewarded. I thought the best way to do this, performance-wise is to write a little function in postgresql and then call for every level that I want to update. I have written a function but when I try to run I get this error-message: PostgreSQL query failed: ERROR: parse error at or near "" , and that isn´t very helpful.
This what thr fucntion looks like:
CREATE FUNCTION
updatereferralpoints(varchar, float8) RETURNS bool AS
'DECLARE
cur_referrerlevel ALIAS FOR $1;
multiplylevel ALIAS FOR $2;
BEGIN
FOR activeuser IN
(SELECT cur_referrerlevel,sum(todayspoints)*multiplylevel AS points FROM users WHERE todayspoints>0 and cur_referrerlevel!=NULL GROUP BY cur_referrerlevel)
LOOP
UPDATE users SET referralpoints=referralpoints activeuser.points WHERE username=activeuser.referrer END LOOP; return ''t'' END;' LANGUAGE 'plpgsql'
I´ve never written a function in Postgres before so I guess I´ve misunderstood the docs somewhere and I would appreciate if someone could help me out with this. I´m aware of that the function shouldn´t return true everytime if something has gone wrong, but that´s a later problem.
Thanks in advance!
