On Thu, Nov 11, 2010 at 12:34:55PM -0500, Tom Lane wrote: > Thom Brown <[email protected]> writes: > > WITH t AS (UPDATE foo SET col = true) > > SELECT * FROM foo WHERE col = false; > > > ... Wouldn't this be more practical to have foo's UPDATEs applied > > prior to SELECT? Otherwise what would the usecase be? > > If that's what you want, you might as well just issue two separate > statements. There is no use-case for this at all unless the WITH > produces some RETURNING data that the SELECT makes use of.
There are lots of use cases where it does exactly this. One simple example is maintaining a rollup table, so as less-rolled data get deleted, they get aggregated into an INSERT into that table. Think of RRDtool, only with a real data store. Cheers, David. -- David Fetter <[email protected]> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [email protected] iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
