Mercurial > hg > orthanc-databases
diff PostgreSQL/Plugins/GetLastChangeIndex.sql @ 85:1012fe77241c db-changes
new extension implemented for PostgreSQL and SQLite: GetLastChangeIndex
author | Sebastien Jodogne <s.jodogne@gmail.com> |
---|---|
date | Thu, 10 Jan 2019 18:04:12 +0100 |
parents | |
children |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/PostgreSQL/Plugins/GetLastChangeIndex.sql Thu Jan 10 18:04:12 2019 +0100 @@ -0,0 +1,27 @@ +-- In PostgreSQL, the most straightforward query would be to run: + +-- SELECT currval(pg_get_serial_sequence('Changes', 'seq'))". + +-- Unfortunately, this raises the error message "currval of sequence +-- "changes_seq_seq" is not yet defined in this session" if no change +-- has been inserted before the SELECT. We thus track the sequence +-- index with a trigger. +-- http://www.neilconway.org/docs/sequences/ + +INSERT INTO GlobalIntegers +SELECT 6, CAST(COALESCE(MAX(seq), 0) AS BIGINT) FROM Changes; + + +CREATE FUNCTION InsertedChangeFunc() +RETURNS TRIGGER AS $body$ +BEGIN + UPDATE GlobalIntegers SET value = new.seq WHERE key = 6; + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + + +CREATE TRIGGER InsertedChange +AFTER INSERT ON Changes +FOR EACH ROW +EXECUTE PROCEDURE InsertedChangeFunc();