Support returning both OLD and NEW values in UPDATE operations

[epshone]

Feature Request: Support returning both OLD and NEW values in UPDATE operations

Problem

Currently, PostgREST's UPDATE operations can return either the new values (return=representation) or minimal information (return=minimal), but there's no way to get both the old and new values in a single operation. This is particularly useful for:

• Audit logging
• Event systems that need to track changes
• Optimizing applications by avoiding separate SELECT queries
• Maintaining atomicity when both old and new values are needed

Proposed Solution

Add support for returning both OLD and NEW values in UPDATE operations by:

1. Adding a new return preference (e.g., return=both or return=full)
2. Modifying the query builder to use RETURNING OLD.*, NEW.*
3. Structuring the response to clearly separate old and new values

Example Usage

HTTP Request:

http
PATCH /table_name?id=eq.123
Prefer: return=both

Generated SQL:

UPDATE table_name
SET field = 'new_value'
WHERE id = 123
RETURNING OLD., NEW.;

Response:

{
  "old": {
    "id": 123,
    "field": "old_value",
    "updated_at": "2024-03-20T10:00:00Z"
  },
  "new": {
    "id": 123,
    "field": "new_value",
    "updated_at": "2024-03-20T10:01:00Z"
  }
}

Technical Details

• This feature leverages existing PostgreSQL functionality (OLD/NEW references in RETURNING clauses)
• Maintains compatibility with existing return preferences
• Could potentially support column selection for both old and new values

Benefits

1. Performance: Eliminates need for separate SELECT queries to track changes
2. Atomicity: Guarantees consistent view of changes in a single transaction
3. Developer Experience: Simplifies common patterns in change tracking and event systems
4. Resource Efficiency: Reduces database round trips

Potential Concerns

1. Response size might be larger (mitigated by selective column returns)
2. Might need to consider how this interacts with other preferences
3. Client libraries would need updates to properly type the response

Questions

1. Should the feature support selecting different columns for OLD vs NEW?
2. How should this interact with existing select parameters?
3. What's the most intuitive name for this return preference?
4. Should the ability to return only OLD values also be supported?

Would love to hear the community's thoughts on this proposal. Happy to provide additional context or adjust the proposal based on feedback.

[wolfgangwalther]

This feature leverages existing PostgreSQL functionality (OLD/NEW references in RETURNING clauses)

That's a bit of a stretch, given that OLD/NEW for RETURNING has been committed two weeks ago - and won't make it into a stable release before October this year.