Обсуждение: Update HSTORE record and then delete if it is now empty - What is the correct sql?

2013/2/23 Ashwin Jayaprakash <ashwin.jayaprakash@gmail.com>:
(...)
>
> Q2: What the best way to check if an HSTORE is empty? Is this it
> "array_length(akeys(data), 1) is null"?

Just a quick answer to your second question: I suspect it might be more
efficient to check your HSTORE column against an empty HSTORE, e.g.
 WHERE val = ''::HSTORE

HTH

Ian Barwick

Ashwin Jayaprakash <ashwin.jayaprakash@gmail.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

<div dir="ltr">Thanks Tom. I'll try it out soon.<br /></div>