pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsplit function for pl/pgsql
4+ messages / 2 participants
[nested] [flat]
* split function for pl/pgsql
@ 2002-10-02 10:30 Frederic Logier <fred@az-informatique.com>
0 siblings, 1 reply; 4+ messages in thread
From: Frederic Logier @ 2002-10-02 10:30 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: split function for pl/pgsql
@ 2002-10-02 15:44 Joe Conway <mail@joeconway.com>
parent: Frederic Logier <fred@az-informatique.com>
0 siblings, 1 reply; 4+ messages in thread
From: Joe Conway @ 2002-10-02 15:44 UTC (permalink / raw)
To: Frederic Logier <fred@az-informatique.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: split function for pl/pgsql
@ 2002-10-02 16:54 Frederic Logier <fred@az-informatique.com>
parent: Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 4+ messages in thread
From: Frederic Logier @ 2002-10-02 16:54 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: split function for pl/pgsql
@ 2002-10-03 03:14 Joe Conway <mail@joeconway.com>
parent: Frederic Logier <fred@az-informatique.com>
0 siblings, 0 replies; 4+ messages in thread
From: Joe Conway @ 2002-10-03 03:14 UTC (permalink / raw)
To: Frederic Logier <fred@az-informatique.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-10-03 03:14 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-02 10:30 split function for pl/pgsql Frederic Logier <fred@az-informatique.com>
2002-10-02 15:44 ` Joe Conway <mail@joeconway.com>
2002-10-02 16:54 ` Frederic Logier <fred@az-informatique.com>
2002-10-03 03:14 ` Joe Conway <mail@joeconway.com>
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