Postgres SQL - Output Clause into a scalar variable

Question

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.

"sample SP with inout and out parms in Postgres" don't use OUT parameters in a procedure to return something. If you want to return something, use a function. You need to accept that SQL Server is very different from Postgres (or essentially any other DBMS) Migrate your mindset too — user330315
– user330315, Commented Mar 25, 2020 at 15:34

user330315 · Accepted Answer · 2020-03-25 15:27:50Z

2

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}

answered Mar 25, 2020 at 15:27

user330315

Sign up to request clarification or add additional context in comments.

7 Comments

Granger Over a year ago

FYI: The difference is that the MsSql "output" clause can yield multiple rows, not just one, and can be used outside of sprocs/functions (as your doc link suggests). In MsSql, the "output" clause can be used in INSERT as well as UPDATE and allows one to capture both the "before" and "after" values if wanted.

user330315 Over a year ago

@Granger: the returning clause in Postgres does exactly the same when used in SQL (rather than PL/pgSQL): dbfiddle.uk/… But variables can only be used procedural code (e.g. PL/pgSQL) which is what the question was about.

Granger Over a year ago

I was going off the doc link you gave. :) In any case, I'm glad it has equivalent functionality.

user330315 Over a year ago

@Granger: the link to the manual refers to PL/pgSQL - not SQL. Postgres - like essentially every other database except SQL Server - makes a clear distinction between the query language (SQL) and the procedural language (PL/xxx)

Granger Over a year ago

Thanks for the distinction. (You made your answer less useful by introducing PL/pgSQL, since it's obvious now it wasn't needed.)

|

Collectives™ on Stack Overflow

Postgres SQL - Output Clause into a scalar variable

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related