Variable value assignment using RETURNING clause

SqlPostgresqlSql Returning

Sql Problem Overview

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

Sql Solutions

Solution 1 - Sql

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.

Solution 2 - Sql

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 $$
Categories
Recommended Sql Solutions
Recommended Postgresql Solutions
Recommended Sql Returning Solutions

Previous Solution:

ggplot2, facet_grid, free scales?

RGgplot2

Next Solution:

Static variables in C++

C++StaticScope

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionOmuView Question on Stackoverflow
Solution 1 - SqlOMG PoniesView Answer on Stackoverflow
Solution 2 - SqlQwertieView Answer on Stackoverflow