Variable value assignment using RETURNING clause

sql postgresql sql-returning
Omu picture Omu · Sep 10, 2010 · Viewed 45.4k times · Source

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

Answer

OMG Ponies picture OMG Ponies · Sep 10, 2010

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.

Related questions

How to use RETURNING with ON CONFLICT in PostgreSQL?

I have the following UPSERT in PostgreSQL 9.5: INSERT INTO chats ("user", "contact", "name") VALUES ($1, $2, $3), ($2, $1, NULL) ON CONFLICT("user", "contact") DO NOTHING RETURNING id; If there are no conflicts it returns something like this: ---------- | id | ---------- 1 | 50 | ---------- 2 | 51 | ---------- But if …

Read More
Insert data in 3 tables at a time using Postgres

I want to insert data into 3 tables with a single query. My tables looks like below: CREATE TABLE sample ( id bigserial PRIMARY KEY, lastname varchar(20), firstname varchar(20) ); CREATE TABLE sample1( user_id bigserial PRIMARY KEY, sample_id bigint REFERENCES sample, …

Read More
Return updated row attributes on UPDATE

My query is as follow: UPDATE t1 SET t1.foreign_key = (SELECT id FROM t2 WHERE t2.col = %s ) WHERE t1.col = %s How do I return some attributes of the updated row in the table in the same query?

Read More