OUTPUT/RETURNING Clause Support for Inserts/Updates/Deletes

[freakingawesome]

Is there any plan on supporting OUTPUT (SQL Server) or RETURNING (PostgresQL) clauses for mutations? This would, for example, allow me to fetch the GUIDs that were inserted:

Example in SQL Server Dialect that also includes the name as well just to show that it can be a full result set with multiple fields and rows:

CREATE TABLE #Foo ( ID UNIQUEIDENTIFIER, Name NVARCHAR(100) )

INSERT #Foo
OUTPUT INSERTED.ID, INSERTED.Name
VALUES (NEWID(), 'A'), (NEWID(), 'B'), (NEWID(), 'C')

Example output

ID	Name
D5D898B0-DDB0-49DF-B57E-C43E95978274	A
43E82C23-BD3E-45E9-ADBD-F77264C9A795	B
E08C3E50-AC3A-42D9-BFA5-C86E360790AE	C

[ahmad-moussawi]

Yes planned for the the v1.1, but the example you posted cannot be accomplished as is, since the Query Builder does not have any information about the table

[ElementalCyclone]

Yes planned for the the v1.1, but the example you posted cannot be accomplished as is, since the Query Builder does not have any information about the table

i think the given example is complete enough, since we can assume that the programmer stores the CREATE-ed table name and able to pass it to Query builder

and anyway
Has this feature added yet ? is there any update(s) ?

[b-twis]

It should be easy enough to do something like this passing in the requested column (or columns)

return db.Query(table).Output("ID").Output("Name").Insert();

Would be great to have this in my current project.

[NicolasMalo]

This would be useful when deleting rows and wanting information about the deleted rows. Right now I have a workaround of selecting and then deleting in another query. It works, but it's slower since it makes 2 calls to the database.

Definitely would be a great feature to add.

[ahmad-moussawi]

Closing this duplicate of #8

[PhilConnor]

Hi, why is this not a priority?
I thought that avoiding extra roundtrips when doing operations on data was a good practice. Supporting "OUTPUT" or "RETURNING" statements would allow that.
I tried doing a "CombineRaw(" RETURNING *") but SqlKata does not surpport combines on update or inserts...
Any other workaround other than having to append this on the sql string?

[gsfarm]

Yeah, working with Guid / uuid is difficult if the uuid is generated on the server side, for example, by gen_random_uuid, a variant with the suffix SELECT lastval() AS id; does not work. For now solution requires overriding CompileInsertQuery in own PostgresCompiler version.

[deg-mallard]

Is there any news on getting this put into the live published NuGet package? Having the OUTPUT function on SqlServer would be a massive help especially as people move away from using autoincremented Id fields to Unique Identifiers

[JakeAlmer]

for anyone else watching as far as I can tell this done in this PR: #372

unfortunately it shows no signs of getting merged