Re: multiple inserts

Liam Stewart <liams@redhat.com> writes:
> But following up on Tom's latest suggestion, I've implemented it in the
> yacc grammer rather than in the parser.

Oh, that's a cool idea ... I think.  Did you take care that coercion of
values entries still happens correctly?  Given the way that UNION type
resolution is done, UNIONing first and coercing afterwards will not
produce the behavior we want.  An example: suppose f1 is numeric.

INSERT INTO foo(f1) VALUES ('11'), ('22.3');

This should coerce both unknown-type literals to numeric without complaint.
However,

INSERT INTO foo(f1) SELECT '11' UNION ALL SELECT '22.3';

will fail, since the UNION outputs will be resolved as type "text" for
lack of any context to decide otherwise, and then it's too late to go
back and make them numeric instead.  The knowledge of the intended
destination column types needs to get in there before the UNION type
resolution code is run.

Currently, there's a special-case hack in transformInsertStmt that fixes
this problem for unknown-type literals in the simple INSERT...SELECT
case.  That won't work as-is for a UNION, but maybe you could resolve
both issues if you reached in and transformed the targetlist column
types before allowing normal transformation of the SELECT part to
proceed.

This is all pretty ugly in any case :-( ... we're really starting to
push the limits of what we can accomplish without redoing the Querytree
datastructure.


> The only problem that I've encountered is that the rules regression test
> is failing on the last select statement - the rule definitions are
> displayed using SELECTs rather than VALUES. They are equivalent, but is
> that re-writing alright?

I think this is fine; changing the expected output of that regression
test is perfectly okay (and happens regularly).  I'm more concerned
about the datatype coercion issue.

            regards, tom lane