[SQL] split function for pl/pgsql

[Frederic Logier]

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 '
 DECLARE
func_nomALIAS FOR $1;
func_prenom ALIAS FOR $2;
func_adresse1   ALIAS FOR $3;
func_adresse2   ALIAS FOR $4;
func_ville  ALIAS FOR $5;
func_cp ALIAS FOR $6;
func_pays   ALIAS FOR $7;
func_email  ALIAS FOR $8;
func_telephone  ALIAS FOR $9;
func_gsmALIAS FOR $10;
func_commentaireALIAS FOR $11;
func_cv ALIAS FOR $12;
func_nom_fichierALIAS FOR $13;
func_iddiplome  ALIAS FOR $14;
func_idqualificationALIAS 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);
ELSE
INSERT 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


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] split function for pl/pgsql

[Frederic Logier]

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


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]