David Fetter wrote:
> On Mon, Feb 26, 2007 at 11:14:01PM -0500, Tom Lane wrote:
>> Rusty Conover <rconover@infogears.com> writes:
>>> Or allow delete and update to be used in sub-queries?
>> That's been discussed but the implementation effort seems far from
>> trivial. One big problem is that a sub-query can normally be
>> re-executed multiple times, eg on the inner side of a join; whereas
>> that's clearly not acceptable for an insert/update/delete.
>
> What kinds of machinery would be needed in order for certain kinds of
> subqueries to get executed only once and have the results cached?
>
> <brain_storm>
>
> INSERT/UPDATE/DELETE ... RETURNING wouldn't be the only possible uses
> of such machinery. A data-changing function in a subquery could be
> another. Maybe there could be some way to mark functions as "execute
> once per subquery."

Is "execute only once" even well-defined for subqueries? Take for example

select * from t1, (delete from t2 returning t2.t1_id) where t1.id =
t2.t1_id ;

Will this delete all record from t2, or just those records for which
a matching record in t1 exists? In case you vote for "all records"
above, now take

select * from t1, (delete from t2 returning t2.t1_id) where t1.id =
t2.t1_id limit 1 ;

I for my part couldn't even say what I'd expect that query to do.
Do other databases support this?

greetings, Florian Pflug

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings