DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=kyW34tzbYDhIm9CAFf3hg3V5TMsQS+9Mx0U+2ylCPNNeZZPWne+8809IEcL2nD2Z4qJtS2iAsdviz/f0QZOc/TWOoJFz8QRo+YKq2u5pYPDjj5uEEroPQX8VU2SqNw+fNKUZXkL0b9miCa9WrBk0iH41LblgZNcH0z/tHUYYZho=
Message-ID: <6d8daee305052411157a750bc7@mail.gmail.com>
Date: Tue, 24 May 2005 11:15:12 -0700
From: Tony Wasson <ajwasson@gmail.com>
Reply-To: Tony Wasson <ajwasson@gmail.com>
To: ogjunk-pgjedan@yahoo.com
Subject: Re: Help: Function for splitting VARCHAR column and migrating its
	data to 2 new tables
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20050524044237.64693.qmail@web31114.mail.mud.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <20050524044237.64693.qmail@web31114.mail.mud.yahoo.com>

On 5/23/05, ogjunk-pgjedan@yahoo.com <ogjunk-pgjedan@yahoo.com> wrote:
> I am restructuring my DB schema and need help migrating data from 1
> column of an existing table to two new tables.  I have some Java code
> that can do this for me, but it's very slow, and I am now hoping I can
> migrate this data with some clever SQL instead.
>=20
> Here are my 3 tables:
>=20
> user_data (existing, old table)
> ---------
>   id           (PK),
>   user_id      (FK)
>   keywords     VARCHAR(256)
>     -- this contains comma separated keywords
>     -- e.g. "new york,san francisco, dallas, food"
>     -- also "keywords without strings are really just 1 keyword"
>   add_date     TIMESTAMP
>=20
>=20
> So now I'm trying to migrate this "keywords" VARCHAR column to a more
> normalized schema:
>=20
> user_data_keyword (new lookup table to populate)
> -----------------
>   id           (PK) -- I may change PK to PK(user_data_id, keyword_id)
>   user_data_id (FK)
>   keyword_id   (FK)
>=20
>=20
> keyword (new table to populate)
> -------
>   id           (PK)
>   name         VARCHAR(64) NOT NULL UNIQUE
>   add_date     TIMEZONE
>=20
>=20
> I just found
> http://www.postgresql.org/docs/current/static/functions-string.html ,
> but if anyone could lend me a hand by getting me started with writing a
> function for this, I'd really appreciate it.
>=20
> Thanks,
> Otis
>=20

I am not aware of any extremely clever SQL to make this ALL happen.
However you can do everything with pl/pgsql. My contribution below
will build the keyword table for you. Once you have this working and
you understand it, you could extend it to build your user_data_keyword
 table.

CREATE OR REPLACE FUNCTION split_on_commas(TEXT) RETURNS SETOF TEXT
LANGUAGE 'plpgsql' AS '
DECLARE
   mystring  ALIAS FOR $1;
   incomma   BOOLEAN :=3D FALSE;       -- ## Catch the first word
   endpos    INTEGER; -- end character
   startpos  INTEGER :=3D 0;
   pos       INTEGER;
   outstring TEXT;
BEGIN
   SELECT INTO endpos CHAR_LENGTH(mystring);
   FOR pos IN 1 .. endpos
   LOOP
       -- There are 2 single quotes, a space, and 2 single quotes below:
       IF SUBSTRING(mystring,pos,1) =3D  '','' THEN
           incomma :=3D TRUE;
           outstring :=3D SUBSTRING(mystring,startpos,pos-startpos);
           RETURN NEXT outstring;
       ELSE
           IF incomma IS TRUE THEN
               incomma :=3D FALSE;
               startpos :=3D pos;
           END IF;
       END IF;
   END LOOP;
   IF incomma IS FALSE THEN     -- ## Catch the last phrase
       outstring :=3D SUBSTRING(mystring,startpos,endpos);
       RETURN NEXT outstring;
   END IF;
RETURN;
END;
';

-- try it! You will get a set of keyword all split out
SELECT * FROM split_on_commas('foo1,bar2,foo3');

 From here, we need a migration function that will loop through row(s)
in your table. I also noticed that some of your words may have leading
and trailing spaces. The function drop_first_and_last_space should
take care of that. Notice that I use this function below in the
build_keyword_table function.

CREATE OR REPLACE FUNCTION drop_first_and_last_space(TEXT) RETURNS
TEXT LANGUAGE 'plpgsql' AS '
DECLARE
   in_string ALIAS FOR $1;
   out_msg TEXT;
BEGIN
   out_msg :=3D in_string;
   IF SUBSTRING(out_msg,1,1) =3D  '' '' THEN
       RAISE NOTICE ''dropping leading space'';
       out_msg :=3D substring(out_msg,2,length(out_msg));
   END IF;
   --and the last space
   IF SUBSTRING(out_msg,length(out_msg),1) =3D  '' '' THEN
       RAISE NOTICE ''dropping trailing space'';
       out_msg :=3D substring(out_msg,1,length(out_msg)-1);
   END IF;
RETURN out_msg;
END
';

-- This function uses the INSERT ((SELECT)EXCEPT(SELECT)); syntax
mentioned in http://www.varlena.com/varlena/GeneralBits/19.htm. Note
that I am unsure why I didn't need to use FOR r in EXECUTE ''SELECT...
syntax. But this did work in my small scale test.

CREATE OR REPLACE FUNCTION build_keyword_table(TEXT) RETURNS TEXT
LANGUAGE 'plpgsql' AS '
DECLARE
   match   ALIAS FOR $1;
   rec     RECORD;
   r       RECORD;
   out     TEXT :=3D ''done'';
BEGIN
   FOR rec IN SELECT keywords
       FROM user_data
       WHERE id SIMILAR TO match
       ORDER BY id
   LOOP
       RAISE NOTICE '' working on %'',rec.keywords;
       FOR r IN SELECT drop_first_and_last_space(split_on_commas) AS
kw FROM split_on_commas(rec.keywords)
       LOOP
           RAISE NOTICE ''trying to insert - %'',r.kw;
           INSERT INTO keyword (name) ( (SELECT r.kw) EXCEPT (SELECT
name FROM keyword WHERE name=3Dr.kw));
       END LOOP;
   END LOOP;
RETURN out;
END;
';

Then try it all together, like so:
SELECT build_keyword_table('%');
The input goes to a SIMILAR TO on id - and % means all elements. You
can match a single row by using something like SELECT
build_keyword_table('123');

I also used this as my test data... It worked for me!

CREATE TABLE user_data (
 id           SERIAL,
 user_id      INTEGER,
 keywords     VARCHAR(256) NOT NULL,
 add_date     TIMESTAMP,
 PRIMARY KEY(id)
);

INSERT INTO user_data (keywords) VALUES ('new york,san francisco,
dallas, food');
INSERT INTO user_data (keywords) VALUES ('phoenix, hot, summer, fun');

CREATE TABLE keyword (
 name         VARCHAR(64) NOT NULL,
 id           SERIAL,
 add_date     TIMESTAMP,
 PRIMARY KEY(name)
);
-- todo put a UNIQUE INDEX on keyword (id)

I hope this helps.
Tony Wasson