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


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly

Reply via email to