Abigail writes: > On Thu, Nov 07, 2013 at 01:03:00PM +0000, Smylers wrote: > > > version should start at 1 for each document and be > > I've used triggers to enforce business rules like this in the past
Mark Stringer writes: > I've used BEFORE INSERT triggers to select the max existing version > ID, then increment that by one for the new version of the doc - Jérôme Étévé writes: > On Postgres and triggers. You can have them to run at any time you want: > http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html Thank you all three of you. A trigger to set the document version number when inserting the row should at least mean consistent data is inserted in the first place, which is probably good enough. Here it is in Postgres's own function language, which goes by the awkwardly written name PL/pgSQL: CREATE OR REPLACE FUNCTION set_document_version_num() RETURNS TRIGGER AS $FN$ BEGIN NEW.version := 1 + MAX(version) FROM document_version WHERE document_id = NEW.document_id; RETURN NEW; END; $FN$ LANGUAGE plpgsql; CREATE TRIGGER insert_document_version_num BEFORE INSERT ON document_version FOR EACH ROW EXECUTE PROCEDURE set_document_version_num(); Postgres also supports embedded Perl, so the function can alternatively be written: CREATE EXTENSION plperl; CREATE OR REPLACE FUNCTION set_document_version_num() RETURNS TRIGGER AS $FN$ $result = spi_exec_query (qq[ SELECT MAX(version) FROM document_version WHERE document_id = $_TD->{new}{document_id} ]); $_TD->{new}{version} = 1 + $result->{rows}[0]{max}; 'MODIFY'; $FN$ LANGUAGE plperl; There's no reason to do this, other than this mailing list has “perl” in its name. Theoretically I'd rather write this stuff in a language I already know (Perl) than one I don't (Postgres's procedural extensions to SQL). But where the body of the function is mostly an SQL query, the Perl version seems more awkward. And the overhead of learning the Postgres–Perl glue ($_TD, and spi_exec_query() and its return value data structure) was greater than learning the little bit of PL/pgSQL I needed (mainly to omit the word SELECT from the beginning of the query). Thank you everybody. Smylers -- The UK gov's proposed gagging law will suppress protest and campaigning by charities and political groups. Read more: http://civilsocietycommission.info/ Please sign this petition: http://38d.gs/1cJ9ViA Oxfam • BHA • Quakers * Countryside Alliance • League Against Cruel Sports • CND