From: Joe Conway <mail@joeconway.com>
To: Frederic Logier <fred@az-informatique.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: split function for pl/pgsql
Date: Wed, 02 Oct 2002 08:44:57 -0700
Message-ID: <3D9B1479.2060600@joeconway.com> (raw)
References: <1033554614.16115.45.camel@ghost>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mail@joeconway.com, fred@az-informatique.com
Subject: Re: split function for pl/pgsql
In-Reply-To: <3D9B1479.2060600@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox