I try to do this, but it's a syntax error, what am I doing wrong?
declare myid := insert into oameni values(default,'lol') returning id;
my table:
create table oameni
(
id serial primary key,
name varchar(10)
);
Add a comment
|
2 Answers
You need to use the INTO clause in the RETURNING to set the value being returned into your variable:
DECLARE myid OAMENI.id%TYPE;
INSERT INTO oameni
VALUES
(default,'lol')
RETURNING id INTO myid;
You also need to specify the data type of your variable; I'm glad to see postgresql supports %TYPE and %ROWTYPE.
6 Comments
Omu
i still get a syntax error, is this available only inside a plpgsql function, or is available in normal posgresql also ?
OMG Ponies
@Omu: How are you attempting to run this? And what version of PostgreSQL?
Omu
I have the latest version 9 RC1, and I just pasted your code inside the sql editor
Scott Bailey
You are confusing procedural code w/ SQL. Declaring variables, cursors, loops etc are all procedural and have to be in a function or in 9.0 a DO anonymous block.
OMG Ponies
@Scott Bailey: that's the only use I could see for this, to be using within a function/stored procedure.
|
Adding to the main answer, it's worth noting that if you are doing this outside a stored procedure, you must wrap the code in a "DO" block, like so:
DO $$
DECLARE
myid mytable.id%TYPE;
BEGIN
INSERT INTO mytable (...)
VALUES (...)
RETURNING id INTO myid;
END $$
