Re: Updatable view and default sequence values - Mailing list pgsql-admin

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