shorter way to get new value of serial?

From: Harald Armin Massa <haraldarminmassa(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: shorter way to get new value of serial?
Date: 2005-11-18 13:26:26
Message-ID: 7be3f35d0511180526t69bdd767m@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Richard_D_Levine 2005-11-18 13:28:51 Re: Rebranding PostgreSQL
Previous Message Richard Huxton 2005-11-18 13:23:35 Re: How to debug a locked backend ?