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?
Date: Sat, 23 Feb 2013 05:49:41 -0500
Message-ID: <24976.1361616581@sss.pgh.pa.us> (raw)
In-Reply-To: <CAF9YjSA-wEdcbHitDw19jM6K=giGESiojJ08_coUcLmTHacj3g@mail.gmail.com>
References: <CAF9YjSA-wEdcbHitDw19jM6K=giGESiojJ08_coUcLmTHacj3g@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, ashwin.jayaprakash@gmail.com
Subject: Re: Update HSTORE record and then delete if it is now empty - What is the correct sql?
In-Reply-To: <24976.1361616581@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox