From: | "Bergbom Staffan" <Staffan(dot)Bergbom(at)lm(dot)se> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Question on how to conditionally commit or rollback a sql-request |
Date: | 2010-01-04 22:14:24 |
Message-ID: | E28D4587265E4D44B2858568DA9A44C001A436EA@mbx1.lantm.intra.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi all,
Making an update to a db-table I want to check if it is the same data
that has already been updated within nearest 0.5 minute before
committing
The table contains a timestamp-field, registeredatdatetime, that I try
to use in a before-update-trigger like:
/* TRIGGER: objectdescription_bu */
CREATE OR REPLACE FUNCTION objectdescription_bu() RETURNS trigger AS $$
DECLARE
intval INTEGER;
BEGIN
intval = (current_timestamp - old.registereddatetime);
IF NEW.STATUS = OLD.STATUS or
NEW.POSITIONCODE = OLD.POSITIONCODE and
intval > (interval '30 seconds') then
Commit;
ELSE
Rollback;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER objectdescription_bu
BEFORE UPDATE
ON objectdescription
FOR EACH ROW
EXECUTE PROCEDURE objectdescription_bu();
When I try to do an update like:
update objectdescription
set status = 466
where
customerid = 25 AND
objectnumber = 4;
I get the following error:
ERROR: SPI execute_plan failed executing query "Commit":
SPI_ERROR_TRANSACTION
CONTEXT: Pl/pgSQL function "objectdescription_bu" line 9 at SQL
statement
What does this mean and what should I do to be able to do the desired
check
Regards
Staffan Bergbom
From | Date | Subject | |
---|---|---|---|
Next Message | erobles | 2010-01-04 22:51:36 | ERROR in createlang / ERROR en createlang |
Previous Message | Greg Smith | 2010-01-04 22:02:13 | Re: Upgraded from Leopard to Snow Leopard - PostgreSQL won't start anymore |