Thread: shorter way to get new value of serial?

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.

Is there really one?

Harald

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

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:

> 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.
>
> Is there really one?
>
> Harald
>
> --
> GHUM Harald Massa
> persuasion python postgresql
> Harald Armin Massa
> Reinsburgstraße 202b
> 70197 Stuttgart
> 0173/9409607

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,


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