Mercurial > hg > orthanc-databases

comparison PostgreSQL/Plugins/GetLastChangeIndex.sql @ 85:1012fe77241c db-changes

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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();