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

From Tom Lane
Subject Re: Update HSTORE record and then delete if it is now empty - What is the correct sql?
Date
Msg-id 24976.1361616581@sss.pgh.pa.us
Whole thread Raw
In response to Update HSTORE record and then delete if it is now empty - What is the correct sql?  (Ashwin Jayaprakash <ashwin.jayaprakash@gmail.com>)
Responses Re: Update HSTORE record and then delete if it is now empty - What is the correct sql?
List pgsql-sql
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



pgsql-sql by date:

Previous
From: Ian Lawrence Barwick
Date:
Subject: Re: Update HSTORE record and then delete if it is now empty - What is the correct sql?
Next
From: Misa Simic
Date:
Subject: Re: Summing & Grouping in a Hierarchical Structure