Re: wCTE behaviour

On Thu, Nov 11, 2010 at 12:34:55PM -0500, Tom Lane wrote:
> Thom Brown <thom@linux.com> 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 <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate