Thread: split function for pl/pgsql

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.

Here my function (not tested of course) :


CREATE FUNCTION candidat_valid (varchar ,varchar ,varchar, varchar,
varchar, varchar, varchar, varchar, varchar, varchar, text, oid,
varchar, varchar, varchar) RETURNS boolean AS '    DECLAREfunc_nom        ALIAS FOR $1;func_prenom        ALIAS FOR
$2;func_adresse1       ALIAS FOR $3;func_adresse2        ALIAS FOR $4;func_ville        ALIAS FOR $5;func_cp
ALIASFOR $6;func_pays        ALIAS FOR $7;func_email        ALIAS FOR $8;func_telephone        ALIAS FOR $9;func_gsm
   ALIAS FOR $10;func_commentaire    ALIAS FOR $11;func_cv            ALIAS FOR $12;func_nom_fichier    ALIAS FOR
$13;func_iddiplome     ALIAS FOR $14;func_idqualification    ALIAS FOR $15;
 
new_id    int4;indice int;tableau_diplome        int[];tableau_qualification    int[];    BEGIN
new_id := output of "SELECT nextval('candidat_id_seq')";
IF (func_nom_fichier == NULL) THEN    INSERT INTO candidat (id, nom, prenom, adresse1, adresse2, ville, cp,
pays, email, telephone, gsm, commentaire) VALUES (new_id, func_nom,
func_prenom, func_adresse1, func_adresse2, func_ville, func_cp,
func_pays, func_email, func_telephone, func_gsm, func_commentaire);ELSEINSERT INTO candidat (id, nom, prenom, adresse1,
adresse2,ville, cp,
 
pays, email, telephone, gsm, commentaire, cv, nom_fichier) VALUES
(new_id, func_nom, func_prenom, func_adresse1, func_adresse2,
func_ville, func_cp, func_pays, func_email, func_telephone, func_gsm,
func_commentaire, func_cv, func_nom_fichier);END IF;
indice := 0;
       tableau_diplome := split(',',func_iddiplome);       tableau_qualification := split(',',func_idqualification);
       while (tableau_diplome[indice]) {
        INSERT INTO candidat_diplome (id_candidat, id_diplome) VALUES
(new_id,tableau_diplome[indice]);           indice := indice + 1;       }
       indice := 0;       while (tableau_qualification[indice]) {
        INSERT INTO candidat_qualif (id_candidat, id_qualification)
VALUES (new_id,tableau_qualification[indice]);        indice := indice + 1;       }


 RETURN TRUE;    END;' LANGUAGE 'plpgsql';


-- 
AZ informatique
68 avenue Felix Faure 69003 Lyon
Tel : +33 (0) 472 844 949 direct : +33 (0) 472 844 956
Fax : 04 78 60 40 70

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

Le mer 02/10/2002 à 17:44, Joe Conway a écrit :
> 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.

Great ! have you some example for call a pl/perl function from a
pl/pgsql function ?
And could i use an int array in pl/pgsql returned by the pl/perl
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):

Thanks for your function but i can't use a beta version of postgresql in
production :(


maybe i should use a int array like this example :

create function foo(_int4) returns int2 as'
declare
a alias for $1;
i int:=1;
begin
while a[i] loop
i:=i+1;
end loop;
return i-1;
end;
' language 'plpgsql';

you can call it by:

select foo('{1232131,12312321,3424234}');



--
AZ informatique
68 avenue Felix Faure 69003 Lyon
Tel : +33 (0) 472 844 949 direct : +33 (0) 472 844 956
Fax : 04 78 60 40 70

Frederic Logier wrote:
> Great ! have you some example for call a pl/perl function from a
> pl/pgsql function ?

I don't use PL/Perl, but I think you should be able to do:  SELECT INTO var my_plperl_func(text_to_split);
from within your PL/pgSQL function.

> And could i use an int array in pl/pgsql returned by the pl/perl
> function ?

I don't know if it is possible to construct a PostgreSQL array in pl/perl, but 
I would imagine that should work. Any pl/perl users out there?

Joe