Re: insert with multiple targetLists

Neil Conway <nconway(at)klamath(dot)dyndns(dot)org> writes:
> I've been taking a look at fixing the TODO item:
> o Allow INSERT INTO tab (col1, ..) VALUES (val1, ..), (val2, ..)
> My first plan of attack was to replace the current list of ResTargets
> in InsertStmt with a list of lists.

If you look at the SQL spec, they actually consider VALUES to be a
<table value constructor> which is one of the base cases for <query
expression>. Thus for example this is legal SQL (copied and pasted
straight from the spec):

CONSTRAINT VIEWS_IS_UPDATABLE_CHECK_OPTION_CHECK
CHECK ( ( IS_UPDATABLE, CHECK_OPTION ) NOT IN
( VALUES ( 'NO', 'CASCADED' ), ( 'NO', 'LOCAL' ) ) )

So one should really think of INSERT...VALUES as a form of
INSERT...SELECT rather than a special case of its own. INSERT...SELECT
is currently extremely klugy (look at the hacks in the rewriter for it)
so I think you will not get very far until you redesign the querytree
structure for INSERT...SELECT.

BTW, all the non-trivial cases for VALUES are Full SQL only, not entry
or even intermediate level. So I don't see much point in providing a
half-baked implementation. We've still got lots left to do to cover
all of the entry-SQL spec, and IMHO we ought to focus on that stuff
first...

regards, tom lane