X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 223F49F9B01 for ; Mon, 22 May 2006 13:50:41 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 70107-03 for ; Mon, 22 May 2006 13:50:31 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 296A79F9D99 for ; Mon, 22 May 2006 13:50:31 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id 3857456431; Mon, 22 May 2006 11:50:30 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Mon, 22 May 2006 11:50:28 -0500 Date: Mon, 22 May 2006 11:50:27 -0500 From: "Jim C. Nasby" To: Kouber Saparev Cc: pgsql-admin@postgresql.org Subject: Re: Updatable view and default sequence values Message-ID: <20060522165026.GV64371@pervasive.com> References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060522:postgres@saparev.com::jFIYRJsmozAFX8HZ:00000000000000000 0000000000000000000000005VCb X-Hashcash: 1:20:060522:pgsql-admin@postgresql.org::NhzanqGL2t3GdoWo:00000000000 00000000000000000000000053Gy X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/291 X-Sequence-Number: 21823 On Tue, May 16, 2006 at 04:16:55PM +0300, Kouber Saparev wrote: > The tricky part comes when I try to make my view insertable. Normally > I'd insert without specifying the sequence values, but in some cases I'd > want also to have full control of what's going into the underlying > tables. The thing is that when I try to do it the simple way by > specifying default values in the view itself: > > ALTER TABLE s_purchase ALTER COLUMN purchase_sid SET DEFAULT > NEXTVAL('purchase_purchase_sid_seq'); > ALTER TABLE s_purchase ALTER COLUMN subscription_purchase_sid SET > DEFAULT NEXTVAL('subscription_purchase_subscription_purchase_sid_seq'); You're doing ALTER TABLE on a view? > CREATE RULE s_purchase_insert AS > ON INSERT TO s_purchase DO INSTEAD ( > INSERT INTO purchase > (purchase_sid, data) > VALUES > (NEW.purchase_sid, NEW.pdata); > > INSERT INTO subscription_purchase > (subscription_purchase_sid, purchase_sid, data) > VALUES > (NEW.subscription_purchase_sid, NEW.purchase_sid, NEW.sdata); > ); Why not just use CURRVAL('purchase_purchase_sid_seq') in the rule? -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461