Re: Update HSTORE record and then delete if it is now empty - What is the correct sql?

Ashwin Jayaprakash <ashwin(dot)jayaprakash(at)gmail(dot)com> writes:
> Hi, here's what I'm trying to do:
> - I have a table that has an HSTORE column
> - I would like to delete some key-vals from it
> - If after deleting key-vals, the HSTORE column is empty, I'd like to
> delete the entire row

> with update_qry as(
> update up_del as r
> set data = delete(data, 'c=>678')
> where name = 'cc'
> returning r.*
> )
> delete from up_del
> where name in (select name from update_qry)
> and array_length(akeys(data), 1) is null;

> *Q1: *That DELETE statement does not work

Nope, it won't, because a single query can only update any particular
table row once, and the DELETE plus its WITH clauses is still only a
single query.

If you want "no empty hstore values" to be an invariant of your data
structure, then expecting every update query to implement that correctly
seems like a pretty bad idea anyway. Consider using a trigger to do
that, ie something like BEFORE UPDATE FOR EACH ROW DO "if new hstore
value is null then delete the row and return null".

A problem with that approach is that the returned count of updated rows
won't be very meaningful, and RETURNING values likewise. If that's a
problem for you, you could use an AFTER trigger instead, which will be a
little slower but it hides the deletes behind the scenes. (Note: a
DELETE issued in a trigger is a separate query, which is why it doesn't
fall foul of the limitation your WITH query did.)

regards, tom lane