Skip site navigation (1) Skip section navigation (2)

Re: Writeable CTEs and side effects

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jeff Davis <pgsql(at)j-davis(dot)com>
Cc: Peter Eisentraut <peter_e(at)gmx(dot)net>, Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi>, Robert Haas <robertmhaas(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Writeable CTEs and side effects
Date: 2009-10-09 13:35:25
Message-ID: 6804.1255095325@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> On Fri, 2009-10-09 at 02:23 +0300, Peter Eisentraut wrote:
>> I think I'd want "writable subqueries" instead of only "writable CTEs".

> I think the original motivation was that it's more clear that a CTE is
> separated and can only be executed once (if it has side effects).
> Depending on how the query is written, it might be less obvious how many
> times the subquery should be executed, and it might change based on the
> plan.

Right.  The behavior would be entirely unpredictable, and usually
undesirable, if the RETURNING query is underneath a join, or an
aggregate, or a LIMIT, yadda yadda.  Tying it to WITH provides a
convenient way, from both the user-visible and implementation sides,
of saying "this is an independent query that we will execute once
and then make the RETURNING results available for use in this other
query".

			regards, tom lane

In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2009-10-09 13:37:34
Subject: Re: COPY enhancements
Previous:From: Albe LaurenzDate: 2009-10-09 11:47:59
Subject: Re: Rejecting weak passwords

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group