Hi all,

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. The problem with that approach is
that:

    (a) the InsertStmt is converted to a Query. I could also change Query
        to use a list of lists (instead of a list) for holding TargetEntry
        items, but that would be ugly (since Query is generic, and this
        would only be needed for Inserts)

    (b) modifying Query would mean a lot of work (e.g. in the rewriter),
        adapting all the places that expect targetList to be a list to
        instead use a list of lists. Once again, this would be messy.

So, that seems like a bad idea.

ISTM that a better way to do this would be to parse the InsertStmt,
and then execute an INSERT for every targetList in the query.
For example:

    INSERT INTO t1 (c1) VALUES (1), (2);

    would be executed in a similar fashion to:

    INSERT INTO t1 (c1) VALUES (1);
    INSERT INTO t1 (c1) VALUES (2);

Does this sound reasonable?

Any suggestions would be welcome.

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Wed, 1 May 2002 19:16:56 -0400
Neil Conway <nconway@klamath.dyndns.org>
insert with multiple targetLists
>     INSERT INTO t1 (c1) VALUES (1), (2);
>
>     would be executed in a similar fashion to:
>
>     INSERT INTO t1 (c1) VALUES (1);
>     INSERT INTO t1 (c1) VALUES (2);
>
> Does this sound reasonable?

I debated doing the above too.  In fact, I had a partial
implementation at one point.

However, the resulting purpose of allowing such a construct is to
enable the speeds copy achieves with the variation that is found in an
insert.  So, the above transformation method really doesn't accomplish
much except a new style for many inserts.   But it is quite a bit
easier simply to code each insert individually if there is a minimal
speed gain.  Large strings may reach query length limits in other
systems using this style (look at a MySQL dump sometime).  You're
really only good for about 50 or 60 records in a single insert
statement there.

I'd tend to run it like a copy that can resolving expressions and
defaults.


Wed, 1 May 2002 19:51:46 -0400
"Rod Taylor" <rbt@zort.ca>
Re: insert with multiple targetLists
Neil Conway <nconway@klamath.dyndns.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

Wed, 01 May 2002 22:58:16 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: insert with multiple targetLists 
Rod Taylor wrote:
> >     INSERT INTO t1 (c1) VALUES (1), (2);
> >
> >     would be executed in a similar fashion to:
> >
> >     INSERT INTO t1 (c1) VALUES (1);
> >     INSERT INTO t1 (c1) VALUES (2);
> >
> > Does this sound reasonable?


Sounds good to me.

> I debated doing the above too.  In fact, I had a partial
> implementation at one point.
> 
> However, the resulting purpose of allowing such a construct is to
> enable the speeds copy achieves with the variation that is found in an
> insert. ...

I thought the purpose of the item was merely for compatibility with
other databases that support this syntax.  I don't think it will ever
match COPY performance, and I don't think stuffing a huge INSERT into
the database rather than COPY rows will ever be a preferred method.

I only see VALUES used by INSERT so if you can think of a clean way to
make that work as multiple INSERTs, I think it would be a good idea. 
Hopefully, it will be one localized change, and we can remove it if we
ever want to support VALUES in more complex situations, as Tom
mentioned.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sun, 2 Jun 2002 01:53:11 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: insert with multiple targetLists