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
