pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jim C. Nasby <jnasby@pervasive.com>
To: Kouber Saparev <postgres@saparev.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Updatable view and default sequence values
Date: Mon, 22 May 2006 14:56:55 -0500
Message-ID: <20060522195655.GO64371@pervasive.com> (raw)
In-Reply-To: <e4t3gt$gd7$1@sea.gmane.org>
References: <e4cjbs$29i$1@sea.gmane.org>
	<20060522165026.GV64371@pervasive.com>
	<e4t3gt$gd7$1@sea.gmane.org>

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



view thread (5+ messages)  latest in thread

Message-ID: <20060522195655.GO64371@pervasive.com>
Permalink:  ../20060522195655.GO64371@pervasive.com/
Also on:    postgresql.org/message-id/20060522195655.GO64371@pervasive.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: jnasby@pervasive.com, postgres@saparev.com
  Subject: Re: Updatable view and default sequence values
  In-Reply-To: <20060522195655.GO64371@pervasive.com>
  References: <e4cjbs$29i$1@sea.gmane.org> <20060522165026.GV64371@pervasive.com> <e4t3gt$gd7$1@sea.gmane.org> <20060522195655.GO64371@pervasive.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox