Re: [GENERAL] Sequence value

[Esa Pikkarainen]

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');

Re: [GENERAL] Sequence value

[Alessio Bragadini]

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[EMAIL PROTECTED]
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

Re: [GENERAL] Sequence value

[Roger Wernersson]

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