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
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