Database Design Advice

[Smylers]

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