Thread: INSERT ... SELECT nonpositional syntax
I'm a PostgreSQL novice but a very, very experienced DBA from the SAP SQL Anywhere context (formerly Sybase / iAnywhere). Most of my code has been in the Watcom SQL dialect in the past.
SAP's products have a nice feature not found in the ANSI standards for the INSERT ... SELECT syntax: WITH AUTO NAME. Assume you have a table that looks something like this:
In PostgreSQL, you can:
INSERT INTO TableX
(
i,
j,
k
)
SELECT a,
b,
c
FROM TableY; -- or whatever SELECT expression you want returning possibly many rows
This is nice, but it's so much nicer to have the destination columns adjacent to your source values:
INSERT INTO TableX
WITH AUTO NAME
SELECT a i,
b j,
c k
FROM TableY;
I'm not complaining here, but just wondering if there is a way that the syntax can be extended at this level using an extension, or if there is a fighting chance of extending the syntax in the base product?
Thanks,
Matthew Mellon
Matthew Mellon <matthew.mellon@gmail.com> writes:
> SAP's products have a nice feature not found in the ANSI standards for the
> INSERT ... SELECT syntax: WITH AUTO NAME. Assume you have a table that
> looks something like this:
> ...
> INSERT INTO TableX
> WITH AUTO NAME
> SELECT a i,
> b j,
> c k
> FROM TableY;
> I'm not complaining here, but just wondering if there is a way that the
> syntax can be extended at this level using an extension, or if there is a
> fighting chance of extending the syntax in the base product?
I recall that we've discussed having some way to do this; but not with
that particular syntax. If memory serves, the last proposal looked
more like UPDATE ... SET, that is
INSERT INTO TableX SET i = a, j = b, k = c FROM TableY;
The general consensus though seems to be that we don't want to get
out front of the SQL standards committee on something like this.
If we do, for sure they'll standardize some other way to do
the same thing :-(
regards, tom lane