CODEWITHSUNDEEP
postgresqlstored-procedures
Chetan_Vasudevan
Chetan_Vasudevan

Reputation: 2414

Postgres SQL - Output Clause into a scalar variable

For the below code which is in SQL server needs to be converted into PostgresSQL. I did try the same way we do in SQL Server but it dint work.

declare @ID table (ID int)

insert into MyTable(ID)
output inserted.ID into @ID
values (1)

So the above code works well in SQL server but when it comes to Postgres it does not work.

Can someone help in converting this code to Postgres ? Also can someone help me in getting a sample SP with inout and out parms in Postgres please.

Upvotes: 0

Views: 1511

Answers (1)

user330315
user330315

Reputation:

Assuming this is part of a stored function (or procedure) written in PL/pgSQL, you can use the returning clause as documented in the manual:

....
declare
  l_generated_id int;
begin
  ...
  insert into my_table(id) 
  values (1)
  returning id into l_generated_id;
  ...
end;

But I have to admit that this seems rather unnecessary as the inserted value is hardcoded in the INSERT statement

Upvotes: 2

Related Questions