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

Reply via email to