David Fetter wrote:
On Mon, Feb 26, 2007 at 11:14:01PM -0500, Tom Lane wrote:
Rusty Conover <[EMAIL PROTECTED]> 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