PL/PGSQL Function
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
Michael Fork <mfork@toledolink.com> writes:
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.
Wild guess here: is it possible that the file you keep the function
definition in contains newlines represented as CR/LF or even just CR,
instead of the Unix-standard LF?
I'm not sure whether the plpgsql parser is flexible about this or not.
If not, it probably should be --- but I can see how a parser complaint
about an unexpected CR might cause the sort of overprinting you
describe.
regards, tom lane