Mercurial > hg > orthanc-databases

annotate PostgreSQL/Plugins/GetLastChangeIndex.sql @ 345:76d53dcfb92b OrthancMySQL-3.0

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
closing OrthancMySQL-3.0
author Sebastien Jodogne <s.jodogne@gmail.com>
date Thu, 12 Aug 2021 16:08:58 +0200
parents 1012fe77241c
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
85
1012fe77241c new extension implemented for PostgreSQL and SQLite: GetLastChangeIndex
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
1 -- In PostgreSQL, the most straightforward query would be to run:
1012fe77241c new extension implemented for PostgreSQL and SQLite: GetLastChangeIndex
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
2
1012fe77241c new extension implemented for PostgreSQL and SQLite: GetLastChangeIndex
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
3 -- SELECT currval(pg_get_serial_sequence('Changes', 'seq'))".
1012fe77241c new extension implemented for PostgreSQL and SQLite: GetLastChangeIndex
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
4
1012fe77241c new extension implemented for PostgreSQL and SQLite: GetLastChangeIndex
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
5 -- Unfortunately, this raises the error message "currval of sequence
1012fe77241c new extension implemented for PostgreSQL and SQLite: GetLastChangeIndex
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
6 -- "changes_seq_seq" is not yet defined in this session" if no change
1012fe77241c new extension implemented for PostgreSQL and SQLite: GetLastChangeIndex
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
7 -- has been inserted before the SELECT. We thus track the sequence
1012fe77241c new extension implemented for PostgreSQL and SQLite: GetLastChangeIndex
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
8 -- index with a trigger.
1012fe77241c new extension implemented for PostgreSQL and SQLite: GetLastChangeIndex
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
9 -- http://www.neilconway.org/docs/sequences/
1012fe77241c new extension implemented for PostgreSQL and SQLite: GetLastChangeIndex
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
10
1012fe77241c new extension implemented for PostgreSQL and SQLite: GetLastChangeIndex
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
11 INSERT INTO GlobalIntegers
1012fe77241c new extension implemented for PostgreSQL and SQLite: GetLastChangeIndex
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
12 SELECT 6, CAST(COALESCE(MAX(seq), 0) AS BIGINT) FROM Changes;
1012fe77241c new extension implemented for PostgreSQL and SQLite: GetLastChangeIndex
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
13
1012fe77241c new extension implemented for PostgreSQL and SQLite: GetLastChangeIndex
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
14
1012fe77241c new extension implemented for PostgreSQL and SQLite: GetLastChangeIndex
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
15 CREATE FUNCTION InsertedChangeFunc()
1012fe77241c new extension implemented for PostgreSQL and SQLite: GetLastChangeIndex
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
16 RETURNS TRIGGER AS $body$
1012fe77241c new extension implemented for PostgreSQL and SQLite: GetLastChangeIndex
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
17 BEGIN
1012fe77241c new extension implemented for PostgreSQL and SQLite: GetLastChangeIndex
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
18 UPDATE GlobalIntegers SET value = new.seq WHERE key = 6;
1012fe77241c new extension implemented for PostgreSQL and SQLite: GetLastChangeIndex
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
19 RETURN NULL;
1012fe77241c new extension implemented for PostgreSQL and SQLite: GetLastChangeIndex
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
20 END;
1012fe77241c new extension implemented for PostgreSQL and SQLite: GetLastChangeIndex
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
21 $body$ LANGUAGE plpgsql;
1012fe77241c new extension implemented for PostgreSQL and SQLite: GetLastChangeIndex
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
22
1012fe77241c new extension implemented for PostgreSQL and SQLite: GetLastChangeIndex
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
23
1012fe77241c new extension implemented for PostgreSQL and SQLite: GetLastChangeIndex
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
24 CREATE TRIGGER InsertedChange
1012fe77241c new extension implemented for PostgreSQL and SQLite: GetLastChangeIndex
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
25 AFTER INSERT ON Changes
1012fe77241c new extension implemented for PostgreSQL and SQLite: GetLastChangeIndex
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
26 FOR EACH ROW
1012fe77241c new extension implemented for PostgreSQL and SQLite: GetLastChangeIndex
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
27 EXECUTE PROCEDURE InsertedChangeFunc();