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