Mercurial > hg > orthanc-databases
comparison 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 |
comparison
equal
deleted
inserted
replaced
82:122f22550521 | 85:1012fe77241c |
---|---|
1 -- In PostgreSQL, the most straightforward query would be to run: | |
2 | |
3 -- SELECT currval(pg_get_serial_sequence('Changes', 'seq'))". | |
4 | |
5 -- Unfortunately, this raises the error message "currval of sequence | |
6 -- "changes_seq_seq" is not yet defined in this session" if no change | |
7 -- has been inserted before the SELECT. We thus track the sequence | |
8 -- index with a trigger. | |
9 -- http://www.neilconway.org/docs/sequences/ | |
10 | |
11 INSERT INTO GlobalIntegers | |
12 SELECT 6, CAST(COALESCE(MAX(seq), 0) AS BIGINT) FROM Changes; | |
13 | |
14 | |
15 CREATE FUNCTION InsertedChangeFunc() | |
16 RETURNS TRIGGER AS $body$ | |
17 BEGIN | |
18 UPDATE GlobalIntegers SET value = new.seq WHERE key = 6; | |
19 RETURN NULL; | |
20 END; | |
21 $body$ LANGUAGE plpgsql; | |
22 | |
23 | |
24 CREATE TRIGGER InsertedChange | |
25 AFTER INSERT ON Changes | |
26 FOR EACH ROW | |
27 EXECUTE PROCEDURE InsertedChangeFunc(); |