pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL stored function inserting and returning data in a row.
8+ messages / 6 participants
[nested] [flat]
* SQL stored function inserting and returning data in a row.
@ 2008-01-11 03:23 Daniel Caune <daniel.caune@ubisoft.com>
0 siblings, 4 replies; 8+ messages in thread
From: Daniel Caune @ 2008-01-11 03:23 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL stored function inserting and returning data in a row.
@ 2008-01-11 11:03 Gerardo Herzig <gherzig@fmed.uba.ar>
parent: Daniel Caune <daniel.caune@ubisoft.com>
3 siblings, 1 reply; 8+ messages in thread
From: Gerardo Herzig @ 2008-01-11 11:03 UTC (permalink / raw)
To: Daniel Caune <daniel.caune@ubisoft.com>; +Cc: pgsql-sql
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;
?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] SQL stored function inserting and returning data in a row.
@ 2008-01-11 11:06 Hocine Abir <Hocine.Abir@iutv.univ-paris13.fr>
parent: Daniel Caune <daniel.caune@ubisoft.com>
3 siblings, 0 replies; 8+ messages in thread
From: Hocine Abir @ 2008-01-11 11:06 UTC (permalink / raw)
To: pgsql-admin@postgresql.org
Selon Daniel Caune <daniel.caune@ubisoft.com>:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
Just add a SELECT query like this :
CREATE FUNCTION create_matchmaking_session()
RETURNS bigint
AS $$
INSERT INTO matchmaking_session
VALUES (nextval('seq_matchmaking_session_id'));
SELECT currval('seq_matchmaking_session_id') as session_id;
$$ LANGUAGE SQL;
--
Hocine Abir
IUT de Villetaneuse
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL stored function inserting and returning data in a row.
@ 2008-01-11 11:15 Marcin Stępnicki <mstepnicki@gmail.com>
parent: Daniel Caune <daniel.caune@ubisoft.com>
3 siblings, 0 replies; 8+ messages in thread
From: Marcin Stępnicki @ 2008-01-11 11:15 UTC (permalink / raw)
To: Daniel Caune <daniel.caune@ubisoft.com>; +Cc: pgsql-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;
"
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL stored function inserting and returning data in a row.
@ 2008-01-11 11:19 Marcin Stępnicki <mstepnicki@gmail.com>
parent: Daniel Caune <daniel.caune@ubisoft.com>
3 siblings, 0 replies; 8+ messages in thread
From: Marcin Stępnicki @ 2008-01-11 11:19 UTC (permalink / raw)
To: Daniel Caune <daniel.caune@ubisoft.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL stored function inserting and returning data in a row.
@ 2008-01-11 13:55 Daniel Caune <daniel.caune@ubisoft.com>
parent: Gerardo Herzig <gherzig@fmed.uba.ar>
0 siblings, 2 replies; 8+ messages in thread
From: Daniel Caune @ 2008-01-11 13:55 UTC (permalink / raw)
To: Gerardo Herzig <gherzig@fmed.uba.ar>; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL stored function inserting and returning data in a row.
@ 2008-01-11 14:17 Pavel Stehule <pavel.stehule@gmail.com>
parent: Daniel Caune <daniel.caune@ubisoft.com>
1 sibling, 0 replies; 8+ messages in thread
From: Pavel Stehule @ 2008-01-11 14:17 UTC (permalink / raw)
To: Daniel Caune <daniel.caune@ubisoft.com>; +Cc: Gerardo Herzig <gherzig@fmed.uba.ar>; pgsql-sql
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;
create
or 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL stored function inserting and returning data in a row.
@ 2008-01-14 08:57 Marc Mamin <M.Mamin@intershop.de>
parent: Daniel Caune <daniel.caune@ubisoft.com>
1 sibling, 0 replies; 8+ messages in thread
From: Marc Mamin @ 2008-01-14 08:57 UTC (permalink / raw)
To: Daniel Caune <daniel.caune@ubisoft.com>; Gerardo Herzig <gherzig@fmed.uba.ar>; +Cc: 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;
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2008-01-14 08:57 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-11 03:23 SQL stored function inserting and returning data in a row. Daniel Caune <daniel.caune@ubisoft.com>
2008-01-11 11:03 ` Gerardo Herzig <gherzig@fmed.uba.ar>
2008-01-11 13:55 ` Daniel Caune <daniel.caune@ubisoft.com>
2008-01-11 14:17 ` Pavel Stehule <pavel.stehule@gmail.com>
2008-01-14 08:57 ` Marc Mamin <M.Mamin@intershop.de>
2008-01-11 11:06 ` Hocine Abir <Hocine.Abir@iutv.univ-paris13.fr>
2008-01-11 11:15 ` Marcin Stępnicki <mstepnicki@gmail.com>
2008-01-11 11:19 ` Marcin Stępnicki <mstepnicki@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox