Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 838599FA249 for ; Wed, 13 Dec 2006 08:39:15 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 62036-10 for ; Wed, 13 Dec 2006 08:38:47 -0400 (AST) X-Greylist: delayed 00:29:38.024897 by SQLgrey-1.7.4 Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 320A99FB1DE for ; Wed, 13 Dec 2006 08:38:42 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from smtp-out3.blueyonder.co.uk (smtp-out3.blueyonder.co.uk [195.188.213.6]) by svr4.postgresql.org (Postfix) with ESMTP id 4B0D45AF2CB for ; Wed, 13 Dec 2006 12:09:02 +0000 (GMT) Received: from [172.23.170.141] (helo=anti-virus02-08) by smtp-out3.blueyonder.co.uk with smtp (Exim 4.52) id 1GuSuy-0005Ah-QM for pgsql-admin@postgresql.org; Wed, 13 Dec 2006 12:09:00 +0000 Received: from [82.44.23.191] (helo=demolish1) by asmtp-out5.blueyonder.co.uk with smtp (Exim 4.52) id 1GuSuy-0005Ed-7Q for pgsql-admin@postgresql.org; Wed, 13 Dec 2006 12:09:00 +0000 Message-ID: <004f01c71eaf$790c5570$7d64a8c0@demolish1> From: "Donald Fraser" To: "[ADMIN]" References: Subject: Re: How to enforce the use of the sequence for serial columns ? Date: Wed, 13 Dec 2006 12:08:59 -0000 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1807 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1807 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/183 X-Sequence-Number: 23975 How to enforce the use of the sequence for serial columns ?There are many ways, here are a couple to think about: 1) Revoke all access to the table and then create a VIEW to access the table where by you simply don't use column "id" in the VIEWs insert / update statements. 2) Manually retrieve the serial number from a trigger function and modify column "id" in the function. Regards Donald Fraser ----- Original Message ----- From: Marc Mamin 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 ? 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