And again: 1NF may not be dead

From: Kenneth Downs <firstinit.lastname_at_lastnameplusfam.net>
Date: Tue, 26 Oct 2004 19:01:23 -0400
Message-ID: <e0lmlc.d77.ln_at_mercury.downsfam.net>



Can it be simply that SQL lacks very basic constructs like SPLIT() and LIST()? (or pick your own favorite name)

What if you had these two simple tables:

CREATE TABLE parents (

   parent_id char(5) PRIMARY KEY,
   foo char(5))

CREATE TABLE children (

   parent_id char(5),
   item_id char(5))
   FOREIGN KEY (parent_id) REFERENCES parents (parent_id)

What I'd like is some kind of SQL command that splits arrays and lists into multiple inserts, something like this:

DECLARE ArrayExample char(5)[] = {'abc','xyz','123'}; DECLARE ListExample varchar(50) = 'abc,xyz,123';

INSERT INTO CHILDREN (parent_id,item_id)

    SELECT 'X',SPLIT(ArrayExample)
INSERT INTO CHILDREN (parent_id,item_id)

    SELECT 'X',SPLIT(ListExample,",")

which would result in 3 inserts. There could be sequenced version also. Oh, and a version that would not create duplicates (insert if not there, else update). I suppose while we're dreaming the array could be 2-d also, resulting in hits to multiple columns.

Equally nice would be the opposite, something like this should return a comma-delimited list and/or array:

DECLARE ArrayExample char(5)[]
DECLARE ListExample String; -- Aha! Snuck that in

SELECT ArrayExample = LIST(item_id) FROM children WHERE... SELECT ListExample = LIST(item_id,",") FROM children WHERE...

FWIW, I've always thought that the RDM can pretty much handle anything, but that the language is, believe it or not, too stuck in the RDM, and does not easily translate into other models.

Perhaps Mr. Celko can jump in and tell us if things like this are under consideration at all.

-- 
Kenneth Downs
Use first initial plus last name at last name plus literal "fam.net" to
email me
Received on Wed Oct 27 2004 - 01:01:23 CEST

Original text of this message