Обсуждение: Question on how to conditionally commit or rollback a sql-request
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
Bergbom Staffan wrote: > 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 It's not possible to commit or rollback inside a function. Instead of issuing a rollback, you could raise an exception, and instead of doing a commit, you should do nothing at all. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org