Variable value assignment using RETURNING clause

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

56285 次浏览

小开

最佳答案

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.

小开

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