Re: [SQL] 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;
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] SQL stored function inserting and returning data in a row.
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;
?
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] SQL stored function inserting and returning data in a row.
On Jan 11, 2008 4:23 AM, Daniel Caune [EMAIL PROTECTED] 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; ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] SQL stored function inserting and returning data in a row.
On Jan 11, 2008 4:23 AM, Daniel Caune [EMAIL PROTECTED] wrote: Please ignore my post. I havent' read your message carefully enough. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] SQL stored function inserting and returning data in a row.
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
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] SQL stored function inserting and returning data in a row.
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,10) g(i)) f; count 10 (1 row) Time: 123,532 ms postgres=# select count(*) from (select test2(i) from generate_series(1,10) g(i)) f; count 10 (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,10) g(i)) f; count 10 (1 row) Time: 472,150 ms Regards Pavel Stehule ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] 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
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
