From: | "Marc Mamin" <M(dot)Mamin(at)intershop(dot)de> |
---|---|
To: | "Daniel Caune" <daniel(dot)caune(at)ubisoft(dot)com>, "Gerardo Herzig" <gherzig(at)fmed(dot)uba(dot)ar> |
Cc: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: SQL stored function inserting and returning data in a row. |
Date: | 2008-01-14 08:57:27 |
Message-ID: | CA896D7906BF224F8A6D74A1B7E54AB301750D2D@JENMAIL01.ad.intershop.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-sql |
> What about
> $$
> INSERT INTO .... ;
> select currval('seq_matchmaking_session_id');
> $$ language sql;
>
> ?
Hello,
I'm not sure that this would return the correct id in case of concurrent
calls to your function.
I'm using following kind of function to manage reference tables:
HTH,
Marc Mamin
CREATE TABLE xxx
(
id serial NOT NULL,
mycolumn character varying,
CONSTRAINT xxx_pk PRIMARY KEY (id) ,
CONSTRAINT xxx_uk UNIQUE (mycolumn)
)
CREATE OR REPLACE FUNCTION get_or_insert_id_xxx( input_value varchar)
RETURNS INT AS $$
DECLARE
id_value int;
BEGIN
select into id_value id from xxx where mycolumn = input_value;
IF FOUND THEN
return id_value;
ELSE
insert into xxx ( mycolumn ) values ( input_value );
return id from xxx where mycolumn = input_value;
END IF;
EXCEPTION WHEN unique_violation THEN
return id from xxx where mycolumn = input_value;
END;
$$ LANGUAGE plpgsql;
From | Date | Subject | |
---|---|---|---|
Next Message | Sebastian Reitenbach | 2008-01-14 16:10:20 | trigger run of archive_command? |
Previous Message | Marc Mamin | 2008-01-14 08:39:16 | Re: SQL question: Highest column value of unique column pairs |
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Childs | 2008-01-14 09:44:58 | Re: trigger for TRUNCATE? |
Previous Message | Marc Mamin | 2008-01-14 08:39:16 | Re: SQL question: Highest column value of unique column pairs |