PL/PGSQL Function

Started by Michael Forkover 25 years ago2 messagesgeneral
Jump to latest
#1Michael Fork
mfork@toledolink.com

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Michael Fork (#1)
Re: PL/PGSQL Function

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