RFC: Inserting multiple values via INSERT ... VALUES ...

There are currently a bunch of items on the Todo relating to SQL
INSERT statements, one of which is allowing INSERTs of the following
form:

INSERT INTO tab [(c1, c2, ...)] VALUES (x1, y1, ...), (x2, y2, ...), ...

I had written a quick 'n dirty patch that accomplished this by
splitting an INSERT of multiple tuples into multiple individual
INSERT statements. However, this approach was not the right way to
go, and so I would like to propose the following as a plan for
getting implementing the feature:

Coming out of the parser (modified to handle the new syntax), an
InsertStmt variable will hold a list of lists of ResTargets. These
lists of ResTargets will be transformed one by one into lists of
TargetEntry's. The transformed lists would be kept in the Query node
that represents the INSERT statement in a new structure member.

With the Result node, PostgreSQL currently has support in the executor
for retrieving a single tuple that is not stored in any base
relation. When doing an INSERT of a single tuple via VALUES, the
current scheme suffices. However, for inserting multiple tuples via
INSERT, the use of the Result node doesn't work, AFAICS. While it
should be possible to modify the Result node to handle multiple
tuples, I would rather not use Result as Result is also used for
constant qualifications. It seems cleaner to have a seperate node
structure that deals with tuples that have no real base relation;
there would be no need to deal with extra cruft in Result-related
functions.

I would like to add a new executor node called Values (suggestions for a
better name are welcome -- there is already a Value node) that would
replace the use of Result for insert statements. A tuple would just be a
List of TargetEntry's (like what Result currently does). The Values node
would just keep a List of those and when asked for a tuple, would return
the next element on the List. It would return NULL when done. The planner
would need to know when to create one -- that can be done query_planner()
as a call to a make_values() function if query->valuesList is not null.

Also, at some point, it would be nice to put together a new statement
node type that represents the SQL <query expression>. This node would
be used everywhere that a <query expression> could be used, hiding
the complexity of having to deal with either a SELECT statement or a
VALUES clause. For example, the parser rule for INSERT statements
would be simplified as well as the transformInsertStmt function. I
haven't thought about this much though. I would like to get multiple
insertion working first before looking at simplification.

Comments?

Liam

-- 
Liam Stewart :: Red Hat Canada, Ltd. :: liams@redhat.com