pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Kouber Saparev <postgres@saparev.com>
To: pgsql-admin@postgresql.org
Subject: Re: Updatable view and default sequence values
Date: Tue, 23 May 2006 13:53:50 +0300
Message-ID: <e4upjj$1nb$1@sea.gmane.org> (raw)
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>

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




view thread (5+ messages)

Message-ID: <e4upjj$1nb$1@sea.gmane.org>
Permalink:  ../e4upjj$1nb$1@sea.gmane.org/
Also on:    postgresql.org/message-id/e4upjj$1nb$1@sea.gmane.org

 · 

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: postgres@saparev.com
  Subject: Re: Updatable view and default sequence values
  In-Reply-To: <e4upjj$1nb$1@sea.gmane.org>
  References: <e4cjbs$29i$1@sea.gmane.org> <20060522165026.GV64371@pervasive.com> <e4t3gt$gd7$1@sea.gmane.org> <20060522195655.GO64371@pervasive.com> <e4upjj$1nb$1@sea.gmane.org>

* 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