From: | Kouber Saparev <postgres(at)saparev(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: Updatable view and default sequence values |
Date: | 2006-05-23 10:53:50 |
Message-ID: | e4upjj$1nb$1@sea.gmane.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | j n | 2006-05-23 11:37:58 | localization, encoding problem |
Previous Message | MG | 2006-05-23 08:39:24 | sql-statement blocks maschine |