Message-ID: <3D9B1479.2060600@joeconway.com>
Date: Wed, 02 Oct 2002 08:44:57 -0700
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.1) Gecko/20020826
MIME-Version: 1.0
To: Frederic Logier <fred@az-informatique.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: split function for pl/pgsql
References: <1033554614.16115.45.camel@ghost>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Frederic Logier wrote:
> hi,
> 
> i'm looking for a split function, like perl or php.
> I need doing a pl/pgsql function with a split return an array.
> I must do some loop with this array for mass inserting.
> 
> I think of doing it with pl / perl but I need to do insert and I don't
> find example with pl / perl and sql.

There is no split function built in to PostgreSQL currently. You could write 
it yourself in PL/Perl and use it in the PL/pgSQL function.

In 7.3 (currently in beta) there is a split_part() function which returns just 
one element. I will most likely write a split function for 7.4 to return an 
array, similar to perl and php. In 7.3, the following will do what you want (I 
think):

CREATE OR REPLACE FUNCTION split(text, text)
RETURNS text[] AS '
   DECLARE
     i int := 0;
     word text;
     result text := ''{'';
     result_arr text[];
   BEGIN
     LOOP
       i := i + 1;
       SELECT INTO word split_part($1, $2, i);
       IF word = '''' THEN
         EXIT;
       END IF;
       IF i > 1 THEN
         result := result || '',"'' || word || ''"'';
       ELSE
         result := result || ''"'' || word || ''"'';
       END IF;
     END LOOP;
     result := result || ''}'';
     result_arr := result;
     RETURN result_arr;
   END
' LANGUAGE 'plpgsql';

test=# select split('a,b,c',',');
   split
---------
  {a,b,c}
(1 row)

test=# select a[1] from (select split('a,b,c',',') as a) as t;
  a
---
  a
(1 row)

HTH,

Joe