Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 005D19FA28E for ; Wed, 13 Dec 2006 10:08:54 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 42233-08 for ; Wed, 13 Dec 2006 10:08:44 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mama.jerrysievers.com (adsl-64-118-249-70.netrox.net [64.118.249.70]) by postgresql.org (Postfix) with ESMTP id 04E6F9FA162 for ; Wed, 13 Dec 2006 10:08:43 -0400 (AST) Received: from homie.jerrysievers.com (homie.jerrysievers.com [192.168.1.7]) by mama.jerrysievers.com (8.13.7/8.13.7) with ESMTP id kBDE8RnN023489; Wed, 13 Dec 2006 09:08:27 -0500 Received: from homie.jerrysievers.com (localhost.localdomain [127.0.0.1]) by homie.jerrysievers.com (8.13.7/8.13.7) with ESMTP id kBDE8Rd8027360; Wed, 13 Dec 2006 09:08:27 -0500 Received: (from jerry@localhost) by homie.jerrysievers.com (8.13.7/8.13.7/Submit) id kBDE8Q80027357; Wed, 13 Dec 2006 09:08:26 -0500 To: "Marc Mamin" Cc: Subject: Re: How to enforce the use of the sequence for serial columns ? References: From: Jerry Sievers Date: 13 Dec 2006 09:08:26 -0500 In-Reply-To: Message-ID: Lines: 92 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.4 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/184 X-Sequence-Number: 23976 "Marc Mamin" writes: > I'd like to ensure that nobody provide the ID in an insert statement > when the id is linked to a sequence. > I tried it with a trigger, but the id value is fed before the "BEFORE > INSERT" test is performed (see below)... > > > Any Idea ? Trigger based solution where same trig can be used for any number of tables by changing the parameter. Will throw one of 2 exceptions on failure to use sequence for the insert. create table foo (a serial); psql:q:2: NOTICE: CREATE TABLE will create implicit sequence "foo_a_seq" for serial column "foo.a" CREATE TABLE create function footrig() returns trigger as $$ begin -- may throw currval not defined exception if new.a = currval(tg_argv[0]) then -- currval defined and equal new col value return new; end if; -- currval is defined but not used for this insert raise exception 'Not using default sequence'; end $$ language plpgsql; CREATE FUNCTION create trigger footrig before insert on foo for each row execute procedure footrig('foo_a_seq'); CREATE TRIGGER You are now connected to database "jerry". insert into foo values (1000); psql:q:25: ERROR: currval of sequence "foo_a_seq" is not yet defined in this session CONTEXT: PL/pgSQL function "footrig" line 3 at if insert into foo values (default); INSERT 0 1 insert into foo values (1000); psql:q:27: ERROR: Not using default sequence select * from foo; a --- 1 (1 row) > Cheers, > > Marc > > > > CREATE FUNCTION serialtest() RETURNS trigger AS $serialtest$ > BEGIN > -- Check that the id is provided > IF NEW.id IS NOT NULL THEN > RAISE EXCEPTION 'id will be set from a sequence; do not > provide it!'; > END IF; > > RETURN NEW; > END; > $serialtest$ LANGUAGE plpgsql; > > > CREATE TABLE test_table > ( > id serial primary key, > foo int > ); > > > CREATE TRIGGER test BEFORE INSERT OR UPDATE ON test_table > FOR EACH ROW EXECUTE PROCEDURE serialtest(); > > > insert into test_table(foo)values(1); > > ERROR: id will be set from a sequence; do not provide it! > SQL state: P0001 -- ------------------------------------------------------------------------------- Jerry Sievers 305 854-3001 (home) Production Database Administrator 305 321-1144 (mobil WWW E-Commerce Consultant