problème postgres à l'installation

Installer mon Dolibarr
1

Bonjour je viens d’installer dolibarr version bêta (snapshot de ce matin) avec postgres pour tester dolibarr
j’ai le message d’erreur :

Erreur SQL : DB_ERROR_SYNTAX - ‹ CREATE OR REPLACE FUNCTION date_format(timestamp without time zone, text) RETURNS text AS $$ DECLARE i int := 1; temp text := ‹  ›; c text; n text; res text; BEGIN WHILE i <= pg_catalog.length($2) LOOP c := SUBSTRING ($2 FROM i FOR 1); IF c = ‹ % › AND i != pg_catalog.length($2) THEN n := SUBSTRING ($2 FROM (i + 1) FOR 1); SELECT INTO res CASE WHEN n = ‹ a › THEN pg_catalog.to_char($1, ‹ Dy ›) WHEN n = ‹ b › THEN pg_catalog.to_char($1, ‹ Mon ›) WHEN n = ‹ c › THEN pg_catalog.to_char($1, ‹ FMMM ›) WHEN n = ‹ D › THEN pg_catalog.to_char($1, ‹ FMDDth ›) WHEN n = ‹ d › THEN pg_catalog.to_char($1, ‹ DD ›) WHEN n = ‹ e › THEN pg_catalog.to_char($1, ‹ FMDD ›) WHEN n = ‹ f › THEN pg_catalog.to_char($1, ‹ US ›) WHEN n = ‹ H › THEN pg_catalog.to_char($1, ‹ HH24 ›) WHEN n = ‹ h › THEN pg_catalog.to_char($1, ‹ HH12 ›) WHEN n = ‹ I › THEN pg_catalog.to_char($1, ‹ HH12 ›) WHEN n = ‹ i › THEN pg_catalog.to_char($1, ‹ MI ›) WHEN n = ‹ j › THEN pg_catalog.to_char($1, ‹ DDD ›) WHEN n = ‹ k › THEN pg_catalog.to_char($1, ‹ FMHH24 ›) WHEN n = ‹ l › THEN pg_catalog.to_char($1, ‹ FMHH12 ›) WHEN n = ‹ M › THEN pg_catalog.to_char($1, ‹ FMMonth ›) WHEN n = ‹ m › THEN pg_catalog.to_char($1, ‹ MM ›) WHEN n = ‹ p › THEN pg_catalog.to_char($1, ‹ AM ›) WHEN n = ‹ r › THEN pg_catalog.to_char($1, ‹ HH12:MI:confused:S AM ›) WHEN n = ‹ S › THEN pg_catalog.to_char($1, ‹ SS ›) WHEN n = ‹ s › THEN pg_catalog.to_char($1, ‹ SS ›) WHEN n = ‹ T › THEN pg_catalog.to_char($1, ‹ HH24:MI:confused:S ›) WHEN n = ‹ U › THEN pg_catalog.to_char($1, ‹ ? ›) WHEN n = ‹ u › THEN pg_catalog.to_char($1, ‹ ? ›) WHEN n = ‹ V › THEN pg_catalog.to_char($1, ‹ ? ›) WHEN n = ‹ v › THEN pg_catalog.to_char($1, ‹ ? ›) WHEN n = ‹ W › THEN pg_catalog.to_char($1, ‹ FMDay ›) WHEN n = ‹ w › THEN EXTRACT(DOW FROM $1)::text WHEN n = ‹ X › THEN pg_catalog.to_char($1, ‹ ? ›) WHEN n = ‹ x › THEN pg_catalog.to_char($1, ‹ ? ›) WHEN n = ‹ Y › THEN pg_catalog.to_char($1, ‹ YYYY ›) WHEN n = ‹ y › THEN pg_catalog.to_char($1, ‹ YY ›) WHEN n = ‹ % › THEN pg_catalog.to_char($1, ‹ % ›) ELSE NULL END; temp := temp operator(pg_catalog.||) res; i := i + 2; ELSE temp = temp operator(pg_catalog.||) c; i := i + 1; END IF; END LOOP; RETURN temp; END $$ IMMUTABLE STRICT LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION MONTH(TIMESTAMP without TIME ZONE) RETURNS INTEGER AS $$ SELECT EXTRACT(MONTH FROM $1)::INTEGER; $$ LANGUAGE SQL IMMUTABLE; CREATE OR REPLACE FUNCTION MONTH(TIMESTAMP WITH TIME ZONE) RETURNS INTEGER AS $$ SELECT EXTRACT(MONTH FROM $1)::INTEGER; $$ LANGUAGE SQL STABLE; CREATE OR REPLACE FUNCTION MONTH(DATE) RETURNS INTEGER AS $$ SELECT EXTRACT(MONTH FROM $1)::INTEGER; $$ LANGUAGE SQL IMMUTABLE; CREATE OR REPLACE FUNCTION DAY(TIMESTAMP without TIME ZONE) RETURNS INTEGER AS $$ SELECT EXTRACT(DAY FROM $1)::INTEGER; $$ LANGUAGE SQL IMMUTABLE; CREATE OR REPLACE FUNCTION DAY(TIMESTAMP WITH TIME ZONE) RETURNS INTEGER AS $$ SELECT EXTRACT(DAY FROM $1)::INTEGER; $$ LANGUAGE SQL STABLE; CREATE OR REPLACE FUNCTION DAY(DATE) RETURNS INTEGER AS $$ SELECT EXTRACT(DAY FROM $1)::INTEGER; $$ LANGUAGE SQL IMMUTABLE; › - CREATE OR REPLACE FUNCTION date_format(timestamp without time zone, text) RETURNS text AS $$ DECLARE i int := 1; temp text := ‹  ›; c text; n text; res text; BEGIN WHILE i <= pg_catalog.length($2) LOOP c := SUBSTRING ($2 FROM i FOR 1); IF c = ‹ % › AND i != pg_catalog.length($2) THEN n := SUBSTRING ($2 FROM (i + 1) FOR 1); SELECT INTO res CASE WHEN n = ‹ a › THEN pg_catalog.to_char($1, ‹ Dy ›) WHEN n = ‹ b › THEN pg_catalog.to_char($1, ‹ Mon ›) WHEN n = ‹ c › THEN pg_catalog.to_char($1, ‹ FMMM ›) WHEN n = ‹ D › THEN pg_catalog.to_char($1, ‹ FMDDth ›) WHEN n = ‹ d › THEN pg_catalog.to_char($1, ‹ DD ›) WHEN n = ‹ e › THEN pg_catalog.to_char($1, ‹ FMDD ›) WHEN n = ‹ f › THEN pg_catalog.to_char($1, ‹ US ›) WHEN n = ‹ H › THEN pg_catalog.to_char($1, ‹ HH24 ›) WHEN n = ‹ h › THEN pg_catalog.to_char($1, ‹ HH12 ›) WHEN n = ‹ I › THEN pg_catalog.to_char($1, ‹ HH12 ›) WHEN n = ‹ i › THEN pg_catalog.to_char($1, ‹ MI ›) WHEN n = ‹ j › THEN pg_catalog.to_char($1, ‹ DDD ›) WHEN n = ‹ k › THEN pg_catalog.to_char($1, ‹ FMHH24 ›) WHEN n = ‹ l › THEN pg_catalog.to_char($1, ‹ FMHH12 ›) WHEN n = ‹ M › THEN pg_catalog.to_char($1, ‹ FMMonth ›) WHEN n = ‹ m › THEN pg_catalog.to_char($1, ‹ MM ›) WHEN n = ‹ p › THEN pg_catalog.to_char($1, ‹ AM ›) WHEN n = ‹ r › THEN pg_catalog.to_char($1, ‹ HH12:MI:confused:S AM ›) WHEN n = ‹ S › THEN pg_catalog.to_char($1, ‹ SS ›) WHEN n = ‹ s › THEN pg_catalog.to_char($1, ‹ SS ›) WHEN n = ‹ T › THEN pg_catalog.to_char($1, ‹ HH24:MI:confused:S ›) WHEN n = ‹ U › THEN pg_catalog.to_char($1, ‹ ? ›) WHEN n = ‹ u › THEN pg_catalog.to_char($1, ‹ ? ›) WHEN n = ‹ V › THEN pg_catalog.to_char($1, ‹ ? ›) WHEN n = ‹ v › THEN pg_catalog.to_char($1, ‹ ? ›) WHEN n = ‹ W › THEN pg_catalog.to_char($1, ‹ FMDay ›) WHEN n = ‹ w › THEN EXTRACT(DOW FROM $1)::text WHEN n = ‹ X › THEN pg_catalog.to_char($1, ‹ ? ›) WHEN n = ‹ x › THEN pg_catalog.to_char($1, ‹ ? ›) WHEN n = ‹ Y › THEN pg_catalog.to_char($1, ‹ YYYY ›) WHEN n = ‹ y › THEN pg_catalog.to_char($1, ‹ YY ›) WHEN n = ‹ % › THEN pg_catalog.to_char($1, ‹ % ›) ELSE NULL END; temp := temp operator(pg_catalog.||) res; i := i + 2; ELSE temp = temp operator(pg_catalog.||) c; i := i + 1; END IF; END LOOP; RETURN temp; END $$ IMMUTABLE STRICT LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION MONTH(TIMESTAMP without TIME ZONE) RETURNS INTEGER AS $$ SELECT EXTRACT(MONTH FROM $1)::INTEGER; $$ LANGUAGE SQL IMMUTABLE; CREATE OR REPLACE FUNCTION MONTH(TIMESTAMP WITH TIME ZONE) RETURNS INTEGER AS $$ SELECT EXTRACT(MONTH FROM $1)::INTEGER; $$ LANGUAGE SQL STABLE; CREATE OR REPLACE FUNCTION MONTH(DATE) RETURNS INTEGER AS $$ SELECT EXTRACT(MONTH FROM $1)::INTEGER; $$ LANGUAGE SQL IMMUTABLE; CREATE OR REPLACE FUNCTION DAY(TIMESTAMP without TIME ZONE) RETURNS INTEGER AS $$ SELECT EXTRACT(DAY FROM $1)::INTEGER; $$ LANGUAGE SQL IMMUTABLE; CREATE OR REPLACE FUNCTION DAY(TIMESTAMP WITH TIME ZONE) RETURNS INTEGER AS $$ SELECT EXTRACT(DAY FROM $1)::INTEGER; $$ LANGUAGE SQL STABLE; CREATE OR REPLACE FUNCTION DAY(DATE) RETURNS INTEGER AS $$ SELECT EXTRACT(DAY FROM $1)::INTEGER; $$ LANGUAGE SQL IMMUTABLE;
Erreur SQL : DB_ERROR_22001 - insert into llx_c_actioncomm (id, code, type, libelle, module) values ( 3, ‹ AC_PROP ›, ‹ system ›, ‹ Send commercial proposal by email › ,‹ propal ›); - ERREUR: 22001: valeur trop longue pour le type character varying(30) LOCATION: varchar, varchar.c:595
Connexion au serveur : postgres.worm-fr.com OK
Version de la base 8.4.4
Création des tables et des clés primaires OK
Création des clés étrangères et des index OK
Création des fonctions Erreur
Chargement des données de référence OK

avez vous une idée?

Merci de votre aide
Philippe

2

Peux-tu lancer la requete suivante seule avec pgadmin

CREATE OR REPLACE FUNCTION date_format(timestamp without time zone, text) RETURNS text AS $$ DECLARE i int := 1; temp text := ‹  ›; c text; n text; res text; BEGIN WHILE i <= pg_catalog.length($2) LOOP c := SUBSTRING ($2 FROM i FOR 1); IF c = ‹ % › AND i != pg_catalog.length($2) THEN n := SUBSTRING ($2 FROM (i + 1) FOR 1); SELECT INTO res CASE WHEN n = ‹ a › THEN pg_catalog.to_char($1, ‹ Dy ›) WHEN n = ‹ b › THEN pg_catalog.to_char($1, ‹ Mon ›) WHEN n = ‹ c › THEN pg_catalog.to_char($1, ‹ FMMM ›) WHEN n = ‹ D › THEN pg_catalog.to_char($1, ‹ FMDDth ›) WHEN n = ‹ d › THEN pg_catalog.to_char($1, ‹ DD ›) WHEN n = ‹ e › THEN pg_catalog.to_char($1, ‹ FMDD ›) WHEN n = ‹ f › THEN pg_catalog.to_char($1, ‹ US ›) WHEN n = ‹ H › THEN pg_catalog.to_char($1, ‹ HH24 ›) WHEN n = ‹ h › THEN pg_catalog.to_char($1, ‹ HH12 ›) WHEN n = ‹ I › THEN pg_catalog.to_char($1, ‹ HH12 ›) WHEN n = ‹ i › THEN pg_catalog.to_char($1, ‹ MI ›) WHEN n = ‹ j › THEN pg_catalog.to_char($1, ‹ DDD ›) WHEN n = ‹ k › THEN pg_catalog.to_char($1, ‹ FMHH24 ›) WHEN n = ‹ l › THEN pg_catalog.to_char($1, ‹ FMHH12 ›) WHEN n = ‹ M › THEN pg_catalog.to_char($1, ‹ FMMonth ›) WHEN n = ‹ m › THEN pg_catalog.to_char($1, ‹ MM ›) WHEN n = ‹ p › THEN pg_catalog.to_char($1, ‹ AM ›) WHEN n = ‹ r › THEN pg_catalog.to_char($1, ‹ HH12:MI:confused:S AM ›) WHEN n = ‹ S › THEN pg_catalog.to_char($1, ‹ SS ›) WHEN n = ‹ s › THEN pg_catalog.to_char($1, ‹ SS ›) WHEN n = ‹ T › THEN pg_catalog.to_char($1, ‹ HH24:MI:confused:S ›) WHEN n = ‹ U › THEN pg_catalog.to_char($1, ‹ ? ›) WHEN n = ‹ u › THEN pg_catalog.to_char($1, ‹ ? ›) WHEN n = ‹ V › THEN pg_catalog.to_char($1, ‹ ? ›) WHEN n = ‹ v › THEN pg_catalog.to_char($1, ‹ ? ›) WHEN n = ‹ W › THEN pg_catalog.to_char($1, ‹ FMDay ›) WHEN n = ‹ w › THEN EXTRACT(DOW FROM $1)::text WHEN n = ‹ X › THEN pg_catalog.to_char($1, ‹ ? ›) WHEN n = ‹ x › THEN pg_catalog.to_char($1, ‹ ? ›) WHEN n = ‹ Y › THEN pg_catalog.to_char($1, ‹ YYYY ›) WHEN n = ‹ y › THEN pg_catalog.to_char($1, ‹ YY ›) WHEN n = ‹ % › THEN pg_catalog.to_char($1, ‹ % ›) ELSE NULL END; temp := temp operator(pg_catalog.||) res; i := i + 2; ELSE temp = temp operator(pg_catalog.||) c; i := i + 1; END IF; END LOOP; RETURN temp; END $$ IMMUTABLE STRICT LANGUAGE PLPGSQL;

et dire quel résultat tu as ?

3

Bonjour

Tout d’abord merci pour ton aide rapide
voici le resultat de la requête :
ERREUR: le langage « plpgsql » n’existe pas
HINT: Utiliser CREATE LANGUAGE pour charger le langage dans la base de données.

par contre les requêtes ne retournent rien :
CREATE OR REPLACE FUNCTION date_format(timestamp without time zone, text) RETURNS text AS $$ DECLARE i int := 1; temp text := ‹  ›; c text; n text; res text; BEGIN WHILE i <= pg_catalog.length($2) LOOP c := SUBSTRING ($2 FROM i FOR 1); IF c = ‹ % › AND i != pg_catalog.length($2) THEN n := SUBSTRING ($2 FROM (i + 1) FOR 1); SELECT INTO res CASE WHEN n = ‹ a › THEN pg_catalog.to_char($1, ‹ Dy ›) WHEN n = ‹ b › THEN pg_catalog.to_char($1, ‹ Mon ›) WHEN n = ‹ c › THEN pg_catalog.to_char($1, ‹ FMMM ›) WHEN n = ‹ D › THEN pg_catalog.to_char($1, ‹ FMDDth ›) WHEN n = ‹ d › THEN pg_catalog.to_char($1, ‹ DD ›) WHEN n = ‹ e › THEN pg_catalog.to_char($1, ‹ FMDD ›) WHEN n = ‹ f › THEN pg_catalog.to_char($1, ‹ US ›) WHEN n = ‹ H › THEN pg_catalog.to_char($1, ‹ HH24 ›) WHEN n = ‹ h › THEN pg_catalog.to_char($1, ‹ HH12 ›) WHEN n = ‹ I › THEN pg_catalog.to_char($1, ‹ HH12 ›) WHEN n = ‹ i › THEN pg_catalog.to_char($1, ‹ MI ›) WHEN n = ‹ j › THEN pg_catalog.to_char($1, ‹ DDD ›) WHEN n = ‹ k › THEN pg_catalog.to_char($1, ‹ FMHH24 ›) WHEN n = ‹ l › THEN pg_catalog.to_char($1, ‹ FMHH12 ›) WHEN n = ‹ M › THEN pg_catalog.to_char($1, ‹ FMMonth ›) WHEN n = ‹ m › THEN pg_catalog.to_char($1, ‹ MM ›) WHEN n = ‹ p › THEN pg_catalog.to_char($1, ‹ AM ›) WHEN n = ‹ r › THEN pg_catalog.to_char($1, ‹ HH12:MI:confused:S AM ›) WHEN n = ‹ S › THEN pg_catalog.to_char($1, ‹ SS ›) WHEN n = ‹ s › THEN pg_catalog.to_char($1, ‹ SS ›) WHEN n = ‹ T › THEN pg_catalog.to_char($1, ‹ HH24:MI:confused:S ›) WHEN n = ‹ U › THEN pg_catalog.to_char($1, ‹ ? ›) WHEN n = ‹ u › THEN pg_catalog.to_char($1, ‹ ? ›) WHEN n = ‹ V › THEN pg_catalog.to_char($1, ‹ ? ›) WHEN n = ‹ v › THEN pg_catalog.to_char($1, ‹ ? ›) WHEN n = ‹ W › THEN pg_catalog.to_char($1, ‹ FMDay ›) WHEN n = ‹ w › THEN EXTRACT(DOW FROM $1)::text WHEN n = ‹ X › THEN pg_catalog.to_char($1, ‹ ? ›) WHEN n = ‹ x › THEN pg_catalog.to_char($1, ‹ ? ›) WHEN n = ‹ Y › THEN pg_catalog.to_char($1, ‹ YYYY ›) WHEN n = ‹ y › THEN pg_catalog.to_char($1, ‹ YY ›) WHEN n = ‹ % › THEN pg_catalog.to_char($1, ‹ % ›) ELSE NULL END; temp := temp operator(pg_catalog.||) res; i := i + 2; ELSE temp = temp operator(pg_catalog.||) c; i := i + 1; END IF; END LOOP; RETURN temp;

la requête END $$ IMMUTABLE STRICT LANGUAGE PLPGSQL; retourne l’erreur.
faut il mettre CREATE LANGUAGE PLPGSQL; END $$ IMMUTABLE STRICT LANGUAGE PLPGSQL; ?

Désolé je n’ai pas pgadmin j’ai tapé ta requête dans ma console linux sur le serveur qui héberge le site (le serveur postgres est un autre serveur)

un psql \l donne pour la base en question :
Name | Owner | Encoding | Collation | Ctype | Access privileges
---------------±------------±---------±------------±------------+

doliscimepi | doliscimepi | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | =Tc/doliscimepi doliscimepi=CTc/doliscimepi

La version de postgresql est la 8.4

as tu assez d’info ?

merci

philippe

4

Peux tu lancer la commande
« CREATE LANGUAGE plpgsql; »

Ensuite, essaie à nouveau de lancer le processus d’install.

Sinon, j’ai fait des modifs dans le CVS pour résoudre ce problème je pense.