Mercurial > hg > orthanc-databases

comparison PostgreSQL/Plugins/GetLastChangeIndex.sql @ 97:66fd0d587773

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
integration db-changes->mainline
author Sebastien Jodogne <s.jodogne@gmail.com>
date Mon, 21 Jan 2019 14:38:12 +0100
parents 1012fe77241c
children
comparison
equal deleted inserted replaced
91:1bd538a5a783 97:66fd0d587773
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();