I've been looking at the updatable-cursors patch http://archives.postgresql.org/pgsql-patches/2007-05/msg00264.php which attempts to implement the SQL-spec UPDATE/DELETE WHERE CURRENT OF syntax. It's pretty much of a mess, but there are some salvageable ideas. There are two big things I don't like about it: one is the parsetree representation (fails to reverse-list nicely) and the other is its assumption that updatable cursors must have FOR UPDATE specified. That is not required by the SQL spec AFAICS, and since our version of FOR UPDATE is not really compatible with the spec (they say it lists columns, we say it lists tables), I don't want to require its use in a patch that is supposedly moving towards spec compliance. Another objection is that the patch seriously reduces the previously available functionality of FOR UPDATE by requiring that a cursor mentioning FOR UPDATE be simply updatable (ie, no joins or grouping). That would cause compatibility problems for existing apps.
What I think we could do instead is not change any existing behavior of cursor declarations, but when WHERE CURRENT OF is used, dig through the execution node tree of the cursor to find the scan node for the target table. The "dig" would refuse to descend below join or grouping nodes, and thus implement the restriction that the cursor be simply updatable. This also means that the digging would be cheap enough that it wouldn't be a performance bottleneck. This would be enough to implement SQL92's notion of updatability. SQL2003 has a more complex notion of updatability that can allow updating of join members in some cases. I'm not sure whether execution-tree examination could be extended to handle that, but I suspect that the practical use-cases for WHERE CURRENT OF don't need it anyway. In any case the currently proposed patch hasn't got any clear path to supporting that either. As far as parsetree representation goes, I'm thinking of having the grammar generate a "CurrentOfExpr" node type that just carries the target cursor name, and let that propagate as-is through parse analysis. In the planner or maybe rewriter, expand it to target_table.ctid = pg_current_of_cursor("cursor name", target_table.tableoid) where pg_current_of_cursor is a function (name,oid) returning TID. It would look up the cursor's portal, dig down to find the scan node, and return the CTID of the tuple currently stored in the scan node's scan tuple slot. Passing the tableoid is necessary to handle inheritance cases --- the cursor plan could involve an Append and we have to be able to tell which Append member to look at. Also, the function has to return NULL (preventing a match) when invoked for some other member of the inheritance tree than the one currently being scanned. The reason for the CurrentOfExpr node representation before planning is to have something that ruleutils.c can reverse-list into WHERE CURRENT OF, instead of some nonstandard comparison on ctid. Comments? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly