From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ashwin Jayaprakash <ashwin.jayaprakash@gmail.com>
cc: pgsql-sql@postgresql.org
Subject: Re: Update HSTORE record and then delete if it is now empty - What is
	the correct sql?
In-reply-to: 
 <CAF9YjSA-wEdcbHitDw19jM6K=giGESiojJ08_coUcLmTHacj3g@mail.gmail.com>
References: 
 <CAF9YjSA-wEdcbHitDw19jM6K=giGESiojJ08_coUcLmTHacj3g@mail.gmail.com>
Comments: In-reply-to Ashwin Jayaprakash <ashwin.jayaprakash@gmail.com>
	message dated "Fri, 22 Feb 2013 14:46:47 -0800"
Date: Sat, 23 Feb 2013 05:49:41 -0500
Message-ID: <24976.1361616581@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql