Thread: UPDATE ... SET (a, b, c) = (expr)
Hi, I've been having a look around to see what would it take to implement the syntax in the subject, completing a patch posted by Sussane Ebrecht two years ago. As far as I can tell, the first problem is in the grammar. The SQL spec mandates <multiple value expression> as a production for the expression at the right of the equal sign. This enters a *large* chain of productions that can end up in a lot of things, and for which I don't think we have a production in our grammar. In fact I think we have special cases for many of the things that can be derived from here, so maybe some things will have to be reworked in order to support all the stuff that the standard mandates. I'll refrain from doing this initially, and will focus on getting the simpler cases working. But then there's the matter of how to represent it from the grammar inwards. At first I thought of creating a new parse node type, say MultiResTarget that would replace ResTarget in UpdateStmt, and get it from there into the optimizer and executor. However, this seems to require creating a new node for TargetEntry too, and duplicate logic for all of that. As far as transformTargetList is concerned this wouldn't be much of a problem, but from there onwards it's pretty complex -- it requires a lot of duplicate logic. In fact I think it would cause the current code which is common for UPDATE/INSERT/SELECT/DELETE to split in two, one for UPDATE and one for the rest, which looks ungood. So is this the right way to approach the problem, or am I missing some simpler way? Perhaps, for example, I should be treating normal updates as currently, and add a new member to UpdateStmt that would hold multi-column expressions. But I'm not sure if that would work at all. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera wrote: > But then there's the matter of how to represent it from the grammar > inwards. At first I thought of creating a new parse node type, say > MultiResTarget that would replace ResTarget in UpdateStmt, and get it > from there into the optimizer and executor. Hmm, I should be passing it as a Query node, right? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Hi Alvaro, > >> But then there's the matter of how to represent it from the grammar >> inwards. At first I thought of creating a new parse node type, say >> MultiResTarget that would replace ResTarget in UpdateStmt, and get it >> from there into the optimizer and executor. > > Hmm, I should be passing it as a Query node, right? > I had (rather unsuccessfully) tried my hands on this same TODO a while back. Here is what Tom had to say about my approach: http://www.nabble.com/UPDATE-using-sub-selects-td9935249.html#a16108043 Also attached is the discussion on hackers to provide some more context: http://www.nabble.com/UPDATE-using-sub-selects-td9489091.html Might not be a big help, but just wanted to mention it here.. Regards, Nikhils -- http://www.enterprisedb.com
Alvaro Herrera <alvherre@commandprompt.com> writes: > So is this the right way to approach the problem, or am I missing some > simpler way? See discussions of NikhilS's patch last year. I think the conclusion we had arrived at was that we should refactor the representation of SubLink so that a single-row subselect could be responsible for producing the values of more than one Param (located in more than one place in the query's expression trees). Otherwise you get into having to redefine the representation of targetlists, which will break more code than is pleasant to think about. regards, tom lane