Postgres: Returning Old & New Values

[sdil]

Is your feature request related to a problem? Please describe.

Postgres 18 recently added a new feature: when you mutate a row, you can retrieve both the old and new values in the RETURNING clause. It would be great if I could use this new feature to do value comparisons in my application.

Describe the solution you'd like

Probably a new method like so:

.RETURNING(User.AllColumns.Old, RETURNING(User.AllColumns.New))

p/s: I have a very shallow knowledge about the inner workings of this library

[go-jet]

You can do something close to described solution using CustomExpression:

func OLD(column Column) Expression {
	return CustomExpression(Token("OLD." + column.Name()))
}

func NEW(column Column) Expression {
	return CustomExpression(Token("NEW." + column.Name()))
}

And then use it like:

stmt := User.UPDATE(
		User.FirstName,
		User.LastName,
	).
	MODEL(User{ .... }).
	WHERE(User.UserID.EQ(Int(1))).
	RETURNING(
		OLD(User.FirstName).AS("old.first_name"),
		OLD(User.LastName).AS("old.last_name"),

		NEW(User.FirstName).AS("new.first_name"),
		NEW(User.LastName).AS("new.last_name"),
	)

var dest struct {
	Old model.User `alias: "old.*"`
	New model.User `alias: "new.*"`
}

err := stmt.Query(db, &dest)

[go-jet]

But, I agree we should add support for OLD and NEW in the future.

[sdil]

Thanks for the cide snippet. This should work well for me