Re: wCTE behaviour

On Thu, Nov 11, 2010 at 12:34:55PM -0500, Tom Lane wrote:
> Thom Brown <thom(at)linux(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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