I am trying to create a trigger on a table that will automatically do 1 of
2 things on insert to a table: (1) Update the existing record with new
values, and (2) if the row doesn't exist add it with the given
values. (It is a database that holds RADIUS accounting information, which
when someone logs off adds their usage for the day to a different table)
The trigger and function create without errors, but on insert a
parse error is generated (which I cannot find for the life of me).
Any help would be greatly appreciated!
Thanks
Michael
Parse Error:
++++++++++++++
radius=# INSERT INTO radacct (username, acctstatustype, acctsessiontime,
acctinputoctets, acctoutputoctets) VALUES ('mfork', 'Stop', 3600, 1000,
1000);
NOTICE: plpgsql: ERROR during compile of ti_update near line 1
"RROR: parse error at or near "
++++++++++++++
NOTE: The quotation mark replacing the 'E' is how it is printed in the
error message.
Here is the PL/PGSQL function and trigger:
CREATE FUNCTION ti_update() RETURNS opaque AS '
DECLARE
acct tiacct%ROWTYPE;
BEGIN
IF NEW.acctstatustype = ''Stop'' THEN
SELECT INTO acct *
FROM tiacct
WHERE ti_username = NEW.username
AND ti_date = date(NEW.tstamp) FOR UPDATE;
IF NOT FOUND THEN
INSERT INTO tiacct (ti_username, ti_date, ti_logins,
ti_seconds, ti_octetsin, ti_octetsout)
VALUES (NEW.username, date(NEW.tstamp), 1,
NEW.acctsessiontime, NEW.acctinputoctets,
NEW.acctoutputoctets);
ELSE
UPDATE tiacct
SET ti_logins = (acct.ti_logins + 1),
ti_seconds = (acct.ti_seconds + NEW.acctsessiontime),
ti_octetsin = (acct.ti_octetsin + NEW.acctinputoctets),
ti_octetsout = (acct.ti_octetsout + NEW.acctoutputoctets)
WHERE ti_username = NEW.username
AND ti_date = date(NEW.tstamp);
END IF;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER ti_update BEFORE INSERT ON radacct
FOR EACH ROW EXECUTE PROCEDURE ti_update();
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
