shorter way to get new value of serial?

On 11/18/05, Harald Armin Massa <haraldarminmassa@gmail.com> wrote:

I have a table:

CREATE TABLE rechner
(
id_r int4 NOT NULL DEFAULT
nextval('rechner_id_r_seq'::regclass),
name text,
CONSTRAINT rechner_pkey PRIMARY KEY (id_r)
)
CREATE UNIQUE INDEX rechner_name
ON rechner
USING btree
(name);

and want to have the existing or new id of 'newobjekt'

CREATE OR REPLACE FUNCTION getrechnerid( text)
RETURNS int4 AS
' DECLARE
result int4;
BEGIN
select id_r from rechner where name=upper($1) into result;

IF not FOUND THEN
select nextval(''swcheck_id_check_seq'') into
result;
insert into rechner (id_r, name) values (result, upper($1));
END IF;
return result;
END;
'
LANGUAGE 'plpgsql' VOLATILE;

#############

so on an empty table:

select getrechnerid('LEBERWURST');
-> 1

select getrechnerid('FISCH');
-> 2

select getrechnerid('LEBERWURST');
-> 1

everything is fine. BUT: I feel that this a SO USUAL request (please, give
me the new primary key of that just inserted beast), that there may be a
simpler way, and I am just to blind to see.

this is completely different from what you do in your function...

'LEBERWURST' was not just inserted (at least, not the last inserted)...

last inserted value of a sequence can be obtained with currval('sequence_name')

* but it only works on the session where netxval call was did it and
just after you actually call nextval

Is there really one?

Harald

--
GHUM Harald Massa
persuasion python postgresql
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607

--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

On Fri, Nov 18, 2005 at 02:26:26PM +0100, Harald Armin Massa wrote:

everything is fine. BUT: I feel that this a SO USUAL request (please, give
me the new primary key of that just inserted beast), that there may be a
simpler way, and I am just to blind to see.

See the currval() documents:

http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html

A
--
Andrew Sullivan | ajs@crankycanuck.ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well.
--Dennis Ritchie

You can allocate you're numbers from a sequence...
http://www.postgresql.org/docs/7.4/interactive/functions-sequence.html

Then you can peek at the sequence to see what was last allocated.

Exactly how you do it depends on your circumstances.

On 18 Nov 2005, at 13:26, Harald Armin Massa wrote:

Harald Armin Massa wrote:

I have a table:
CREATE TABLE rechner
(
id_r int4 NOT NULL DEFAULT nextval('rechner_id_r_seq'::regclass),
name text,
CONSTRAINT rechner_pkey PRIMARY KEY (id_r)
)
CREATE UNIQUE INDEX rechner_name
ON rechner
USING btree
(name);

and want to have the existing or new id of 'newobjekt'
CREATE OR REPLACE FUNCTION getrechnerid( text)
RETURNS int4 AS
' DECLARE
result int4;
BEGIN
select id_r from rechner where name=upper($1) into result;

IF not FOUND THEN
select nextval(''swcheck_id_check_seq'') into result;
insert into rechner (id_r, name) values (result, upper($1));

Why don't you just use the default? You could entirely do away with the
'result' variable that way:

CREATE OR REPLACE FUNCTION getrechnerid( text)
RETURNS int4 AS
' BEGIN
select id_r from rechner where name=upper($1) into result;

IF not FOUND THEN
insert into rechner (name) values (upper($1));
END IF;
...

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

//Showing your Vision to the World//

Alban Hertroys,

Why don't you just use the default? You could entirely do away with the

'result' variable that way:

because I need to know which is the new id of that computer. The other way
would be "insert if not exists" and again "select where name = ..." -> 1
statement more, and without a stored procedure even a roundtrip client -
server - client more, which is expensive in WAN situations :(

Harald

--
GHUM Harald Massa
persuasion python postgresql
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607