Re: Sequence value

Esa Pikkarainen wrote:

> but unfortunately it did not solve my problem. Now I have no access
> to the value of nextval('koe_pkey_id'). This query does not return
> any recordset.

Sorry, I didn't check this requirement.

> Is it possible to embed Insert query as a subquery into a Select
> query? I have not managed to dot it. I mean something like:
>
> SELECT nextval('koe_pkey_id') as val, (INSERT INTO koe (id, name)
> values (val, 'uusi')) as dummy FROM koe;"

I don't think so.

> That would both insert new record and return serial value - if it
> just worked...

In one of your examples you used a double statement, so you could
probably try:

INSERT INTO koe (id, name) (SELECT nextval('koe_pkey_id'), 'uusi');
SELECT currval('koe_pkey_id');

This should INSERT and then retrieve the same sequence value. As has
been explained to me before :-) the sequence value is safe (i.e. doesn't
get updated by another connection) inside the connection. One of your
examples was similar, but without the nextval part, so probably the
backend doesn't have a value for currval yet.

--
Alessio F. Bragadini alessio(at)albourne(dot)com
APL Financial Services http://village.albourne.com
Nicosia, Cyprus phone: +357-2-755750

"It is more complicated than you think"
-- The Eighth Networking Truth from RFC 1925