Re: Idea: INSERT INTO ... NATURAL SELECT ...

Hi Steve,

On 01/02/2019 18.32, Steve Midgley wrote:
> On Fri, Feb 1, 2019 at 8:07 AM Sven Berkvens-Matthijsse 
> <sven@postgresql.berkvens.net <mailto:sven@postgresql.berkvens.net>> 
> wrote:
>
>     <snip>
>     INSERT INTO test_table NATURAL SELECT
>            1 AS does_not_exist, 2 AS also_nonexistent;
>
>     INSERT INTO test_table NATURAL SELECT 1 AS a, 2 AS a;
>
>     Anyone with any thoughts about this? An implementation would make
>     inserting data into wide tables by hand very much easier. Because
>     of the
>     placement of the NATURAL keyword, I don't think this will conflict
>     with
>     any current or future proposal from the SQL standard (except maybe
>     for
>     this one :-) ).
>
>
> Hi Sven,
>
> I can clearly see the benefits of your proposal from a custom SQL 
> writing perspective. Personally, I would just write this capability as 
> a DSL in a higher level language and have it translated into the ugly 
> SQL you're trying to avoid (that has the field values and names split 
> far apart, visually).

Sure, that's definitely an option! The INSERT statements I have are 
usually part of larger SQL files that do other things as well, but 
there's no reason I couldn't make a preprocessor or something that 
expands something custom into the usually formatted INSERT statements.

> I have no idea what the implications of your NATURAL keyword on the 
> implementation of Postgres itself, but I do think (as another person 
> who writes manual SQL on occasion) that your proposal has merits for 
> this narrow use-case.

Maybe it's just not useful that often because people don't write much 
SQL by hand these days (maybe they never did, I don't know).

> Personally I don't think the use-case is substantial enough to merit 
> implementing a custom keyword unless there are a lot more people with 
> use-cases that would benefit also. But if you can write an optional 
> module that can be installed outside of core, it seems like why not? 
> Just my two cents.

I'm afraid the plug-in architecture of PostgreSQL wouldn't allow my 
proposal to be implemented that way (unless I'm mistaken and I didn't 
read and/or understand correctly).

> Sincerely,
> Steve


Thanks for your response and thoughts!
Sven