Re: And again: 1NF may not be dead

From: Kenneth Downs <firstinit.lastname_at_lastnameplusfam.net>
Date: Fri, 05 Nov 2004 12:34:04 -0500
Message-ID: <didgmc.llp.ln_at_192.168.10.210>

--CELKO-- wrote:

>>> What I'd like is some kind of SQL command that splits arrays and

> lists into multiple inserts, .. Perhaps Mr. Celko can jump in and tell
> us if things like this are unde r consideration at all. <<
>
> We have it with the table constructor in SQL-92. You just need to
> pass each array elment to a VALUES() clause:
>
> INSERT INTO Foobar VALUES ((:a[1]), (:a[2]),..(:a[100]));
>
> You can get a little fancier with an array records in the host
> language, and you can be safe with a little extra code:
>
> INSERT INTO Foobar
> SELECT DISTINCT x
> FROM VALUES ((:a[1]), (:a[2]),..(:a[100])) AS InputList(x)
> WHERE x IS NOT NULL -- possible rule
> AND {{ other safety predicates on InputList};
>
> --CELKO--
These examples require explicit naming of all values. What would be more in the spirit of the OP would be:

INSERT INTO Foobar (col1,col2,col3) VALUES (a[])

Where a[] must be three columns wide but can have 1..n rows.

-- 
Kenneth Downs
Use first initial plus last name at last name plus literal "fam.net" to
email me

Received on Fri Nov 05 2004 - 18:34:04 CET