Обсуждение: SQL stored function inserting and returning data in a row.

Hi,

Is there any way to define a SQL stored function that inserts a row in a
table and returns the serial generated?

CREATE TABLE matchmaking_session
( session_id bigint NOT NULL DEFAULT
nextval('seq_matchmaking_session_id'), ...
);

CREATE FUNCTION create_matchmaking_sesssion(...) RETURNS bigint
AS $$ INSERT INTO matchmaking_session(...)   VALUES (...)   RETURNING session_id;
$$ LANGUAGE SQL;
2008-01-10 22:08:48 EST ERROR:  return type mismatch in function
declared to return bigint
2008-01-10 22:08:48 EST DETAIL:  Function's final statement must be a
SELECT.
2008-01-10 22:08:48 EST CONTEXT:  SQL function
"create_matchmaking_sesssion"

I can easily convert this code into a PL/pgSQL function, but I'm
thinking that pure SQL is more natural (and faster?) for such a stored
function.

Regards,

--
Daniel

Daniel Caune wrote:

>Hi,
>
>Is there any way to define a SQL stored function that inserts a row in a
>table and returns the serial generated?
>
>CREATE TABLE matchmaking_session
>(
>  session_id bigint NOT NULL DEFAULT
>nextval('seq_matchmaking_session_id'),
>  ...
>);
>
>CREATE FUNCTION create_matchmaking_sesssion(...)
>  RETURNS bigint
>AS $$
>  INSERT INTO matchmaking_session(...)
>    VALUES (...)
>    RETURNING session_id;
>$$ LANGUAGE SQL;
> 
>2008-01-10 22:08:48 EST ERROR:  return type mismatch in function
>declared to return bigint
>2008-01-10 22:08:48 EST DETAIL:  Function's final statement must be a
>SELECT.
>2008-01-10 22:08:48 EST CONTEXT:  SQL function
>"create_matchmaking_sesssion"
>
>  
>
What about
$$
INSERT INTO .... ;
select currval('seq_matchmaking_session_id');
$$ language sql;

?

On Jan 11, 2008 4:23 AM, Daniel Caune <daniel.caune@ubisoft.com> wrote:
> Hi,
>
> Is there any way to define a SQL stored function that inserts a row in a
> table and returns the serial generated?

Maybe you just need INSERT ... RETURNING?

http://www.postgresql.org/docs/8.2/interactive/sql-insert.html

" Insert a single row into table distributors, returning the sequence
number generated by the DEFAULT clause:

INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')  RETURNING did;
"

On Jan 11, 2008 4:23 AM, Daniel Caune <daniel.caune@ubisoft.com> wrote:

Please ignore my post. I havent' read your message carefully enough.

> What about
> $$
> INSERT INTO .... ;
> select currval('seq_matchmaking_session_id');
> $$ language sql;
>
> ?

Indeed... :-( For some reason, I thought that it was not possible to
have to SQL statement in an SQL stored function.

By the way, is there any performance difference between pure SQL and
PL/pgSQL stored functions?  If I remember correctly there was such a
distinction between pure SQL statement and PL/PLSQL stored procedures
(Oracle), in the sense that PL/PLSQL stored procedures are executed
within the PL/PLSQL engine which sends pure SQL statements to the SQL
engine for execution.  There is a little overhead between PL/PLSQL and
SQL engines.

Regards,

--
Daniel

Hello

> By the way, is there any performance difference between pure SQL and
> PL/pgSQL stored functions?  If I remember correctly there was such a
> distinction between pure SQL statement and PL/PLSQL stored procedures
> (Oracle), in the sense that PL/PLSQL stored procedures are executed
> within the PL/PLSQL engine which sends pure SQL statements to the SQL
> engine for execution.  There is a little overhead between PL/PLSQL and
> SQL engines.
>

create or replace function test1(integer)
returns integer as
$$select $1;$$
language sql immutable;

createor replace function test2(integer)
returns integer as
$$begin return  $1; end$$
language plpgsql immutable;

postgres=# select count(*) from (select test1(i) from
generate_series(1,100000) g(i)) f;count
--------100000
(1 row)

Time: 123,532 ms

postgres=# select count(*) from (select test2(i) from
generate_series(1,100000) g(i)) f;count
--------100000
(1 row)

Time: 123,877 ms

but if you forgot immutable
postgres=# create or replace function test3(integer)
returns integer as
$$begin return  $1; end$$
language plpgsql;
CREATE FUNCTION
Time: 430,258 ms
postgres=# select count(*) from (select test3(i) from
generate_series(1,100000) g(i)) f;count
--------100000
(1 row)

Time: 472,150 ms

Regards
Pavel Stehule

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