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 D45539F9B01 for ; Mon, 22 May 2006 16:57:11 -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 19516-08 for ; Mon, 22 May 2006 16:57:01 -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 F02299F9D99 for ; Mon, 22 May 2006 16:57:00 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id 8E28A56427; Mon, 22 May 2006 14:56:58 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Mon, 22 May 2006 14:56:57 -0500 Date: Mon, 22 May 2006 14:56:55 -0500 From: "Jim C. Nasby" To: Kouber Saparev Cc: pgsql-admin@postgresql.org Subject: Re: Updatable view and default sequence values Message-ID: <20060522195655.GO64371@pervasive.com> References: <20060522165026.GV64371@pervasive.com> 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::/CQkazXvUmgTGop4:00000000000000000 0000000000000000000000000MOB X-Hashcash: 1:20:060522:pgsql-admin@postgresql.org::msdwBNdB1WuJCeJo:00000000000 0000000000000000000000006bQZ X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/295 X-Sequence-Number: 21827 On Mon, May 22, 2006 at 10:30:47PM +0300, Kouber Saparev wrote: > Jim C. Nasby wrote: > >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? > > Exactly, AFAIK there's no ALTER VIEW command. > > >>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? > > Because I would like to be able to insert data both by specifying and > without specifying values for primary keys. For example: > > INSERT INTO s_purchase (pdata, sdata) VALUES ('x', 'y'); > > INSERT INTO s_purchase (purchase_sid, pdata, subscription_purchase_sid, > sdata) VALUES (123, 'x', 456, 'y'); > > If I specify CURRVAL and not NEW.primary_key, as you're proposing, I > will lose the second way of adding data, cause in the latter case the > values have nothing to do with the sequences, hence CURRVAL will give me > completely useless, or even worse - wrong data. That's why I'm using > default values of a view - if there's a value provided, it will be > entered as is, if not - then the default value (nextval in this case) > will be taken. > > However, this solution is not robust enough. That's why I'm looking for > other possible solutions. :) I think you could get away with doing a CASE or COALESCE statement, ie: INSERT INTO subscription_purchase ... SELECT COALESCE(currval('purchase_purchase_sid_seq'), NEW.purchase_sid) BTW, it would be interesting to share whatever you finally come up with; it's an interesting problem. -- 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