PostgreSQL - Does a 'RETURNING ELSE' statement exist for an UPDATE?-CodePudding

I have this query:

update client
set start_date = current_date,
email = '[email protected]'
where client_id = 1
returning client_id, username, 1 as isSuccess

When the update is successful, it returns the following:

client_id	username	isSuccess
1	test_name	1

When the update doesn't execute, it returns client_id, username, and isSuccess, but their values are blank.

What I'm having trouble with is customizing what returns when NO update is performed. If no update is performed, I need the following to return:

client_id	username	isSuccess
NULL	NULL	0

Are there any tricks for writing a RETURNING clause with an ELSE clause to get the above result set when no update is executed? OR are there any other methods to get the result set I need? The following code does not work -

 update client
    set start_date = current_date,
    email = '[email protected]'
    where client_id = 1
    returning client_id, username, 1 as isSuccess
else client_id is null, username is null, 0 as isSuccess

CodePudding user response：

There is no ELSE statement in the RETURNING clause, but you can do something similar. The basic idea is to take the result from your update, combine it with another row using a UNION and just return the first result.

Postgres doesn't allow us to use an update statement with a UNION so we have to put the update statement in a CTE:

WITH client_update AS (
  update client
  set start_date = current_date,
  email = '[email protected]'
  WHERE client_id = 2
  returning client_id, username, 1 as isSuccess, 1 as result_order
)
SELECT client_id, username, isSuccess FROM
  (
    SELECT client_id, username, isSuccess, result_order
    FROM client_update
    UNION ALL
    SELECT null, null, 0, 2
  ) sub 
ORDER BY result_order 
LIMIT 1;

I added an addition column, result_order so we can manually specify which result to prefer. In this case, if the returning clause returns a result, we want to return that one, so it gets the 1.