I磎 making some scripts for a affiliate-program for my site and I磎 having a problem with with the script that磗 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磘 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磛e never written a function in Postgres before so I guess I磛e misunderstood the docs somewhere and I would appreciate if someone could help me out with this. I磎 aware of that the function shouldn磘 return true everytime if something has gone wrong, but that磗 a later problem.
Thanks in advance!
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磛e never written a function in Postgres before so I guess I磛e misunderstood the docs somewhere and I would appreciate if someone could help me out with this. I磎 aware of that the function shouldn磘 return true everytime if something has gone wrong, but that磗 a later problem.
Thanks in advance!