Re: SQL stored function inserting and returning data in a row.

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