Обсуждение: Sequence value

So, I want to insert new record and get immediately its sequence
value (serial column) (or OID). I Use iHTML web interface.
Now I have found out that I can give two queries in iHTML like this:

SQL ="SELECT nextval('koe_pkey_id') as val;
      INSERT INTO koe (id, name) values (val, 'uusi');"

Now there are some problems. The previous example causes an error:
ERROR: Attribute 'val' not found
Is there a method how to transmit a value from one query to another?

If I try the examples of FAQ:
SQL ="$newSerialID = nextval('koe_pkey_id');
      INSERT INTO koe (id, name) values ($newSerialID, 'uusi');"
Or
SQL ="INSERT INTO koe (name) values ('uusi');"
      $newSerialID = curr('koe_pkey_id');

I get: ERROR: parser: parse error at or near "$"

And lately (this may be an iHTML problem) if I try:
SQL ="INSERT INTO koe (name) values ('uusi');
      SELECT currval('koe_pkey_id') as val;"
It causes no error, but I cannot retrieve the value because the
queries seem not return any recordsets (nothig can be fetched).

Any hints are valuable!
Thanks
Esa Pikkarainen

Esa Pikkarainen wrote:

> SQL ="SELECT nextval('koe_pkey_id') as val;
>       INSERT INTO koe (id, name) values (val, 'uusi');"

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

--
Alessio F. Bragadini        alessio@albourne.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

Thank you,
Yet another way to do it ;-)
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.
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;"

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

Thanks
Esa

Alessio Bragadini wrote (15 Dec 00,):
> Esa Pikkarainen wrote:
> > SQL ="SELECT nextval('koe_pkey_id') as val;
> >       INSERT INTO koe (id, name) values (val, 'uusi');"

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

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@albourne.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

AAAHHH, I DID IT!!!!

That:
SQL="SELECT nextval('koe_pkey_id');
     INSERT INTO koe (id, name) VALUES
     (SELECT currval('koe_pkey_id'), 'uusi');"

Now it returns the correct Serial value of the record it inserts.

Yee, I'm glad now
:-D

Thank you all and have a good weekend!
Esa Pikkarainen

You could try

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

or something like that.

/Roger

Esa Pikkarainen wrote:
>
> So, I want to insert new record and get immediately its sequence
> value (serial column) (or OID). I Use iHTML web interface.
> Now I have found out that I can give two queries in iHTML like this:
>
> SQL ="SELECT nextval('koe_pkey_id') as val;
>       INSERT INTO koe (id, name) values (val, 'uusi');"
>
> Now there are some problems. The previous example causes an error:
> ERROR: Attribute 'val' not found
> Is there a method how to transmit a value from one query to another?
>
> If I try the examples of FAQ:
> SQL ="$newSerialID = nextval('koe_pkey_id');
>       INSERT INTO koe (id, name) values ($newSerialID, 'uusi');"
> Or
> SQL ="INSERT INTO koe (name) values ('uusi');"
>       $newSerialID = curr('koe_pkey_id');
>
> I get: ERROR: parser: parse error at or near "$"
>
> And lately (this may be an iHTML problem) if I try:
> SQL ="INSERT INTO koe (name) values ('uusi');
>       SELECT currval('koe_pkey_id') as val;"
> It causes no error, but I cannot retrieve the value because the
> queries seem not return any recordsets (nothig can be fetched).
>
> Any hints are valuable!
> Thanks
> Esa Pikkarainen