Subject: split function for pl/pgsql
From: Frederic Logier <fred@az-informatique.com>
To: pgsql-sql@postgresql.org
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: 02 Oct 2002 12:30:14 +0200
Message-Id: <1033554614.16115.45.camel@ghost>
Mime-Version: 1.0

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_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			ALIAS FOR $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);
	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