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 100A99F94EA for ; Tue, 23 May 2006 07:54: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 47007-08 for ; Tue, 23 May 2006 07:53:59 -0300 (ADT) X-Greylist: delayed 15:22:11.006182 by SQLgrey- Received: from ciao.gmane.org (main.gmane.org [80.91.229.2]) by postgresql.org (Postfix) with ESMTP id D802D9F946D for ; Tue, 23 May 2006 07:53:57 -0300 (ADT) Received: from list by ciao.gmane.org with local (Exim 4.43) id 1FiUWN-0002D5-GF for pgsql-admin@postgresql.org; Tue, 23 May 2006 12:53:51 +0200 Received: from pirus.securax.be ([213.16.46.130]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Tue, 23 May 2006 12:53:51 +0200 Received: from postgres by pirus.securax.be with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Tue, 23 May 2006 12:53:51 +0200 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-admin@postgresql.org From: Kouber Saparev Subject: Re: Updatable view and default sequence values Date: Tue, 23 May 2006 13:53:50 +0300 Lines: 44 Message-ID: References: <20060522165026.GV64371@pervasive.com> <20060522195655.GO64371@pervasive.com> Reply-To: postgres@saparev.com Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@sea.gmane.org X-Gmane-NNTP-Posting-Host: pirus.securax.be User-Agent: Mozilla Thunderbird 1.5.0.2 (Windows/20060308) In-Reply-To: <20060522195655.GO64371@pervasive.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/311 X-Sequence-Number: 21843 Jim C. Nasby wrote: > 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. Yeah, as I already wrote in my first mail, that's the workaround I'm currently using - COALESCE everywhere. However, it bothers me that I'm repeating the same expression multiple times. I was thinking also of writing some stored procedure in order to determine whether NEXTVAL was already called and in case it was - to call CURRVAL instead. Something like that: CREATE FUNCTION nextcurrval(x_sequence regclass) RETURNS int8 AS $BODY$ BEGIN RETURN CURRVAL(x_sequence); EXCEPTION WHEN others THEN RETURN NEXTVAL(x_sequence); END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; The thing is that it works only per session and not per SQL statement, i.e. RULE. So, in case I have two or more inserts in one session it will not work correctly - it will always return CURRVAL. BTW, I didn't manage to find out what's the exception error code for the "CURRVAL sequence not yet defined" error - that's why I used 'others'. Anyway, I'll write here when I find other interesting solutions. Regards, -- Kouber Saparev http://kouber.saparev.com