id créer avec exception dans fonction

**gKsam** · 20/10/2010, 18h55

Bonjour,

Bon je débute pas mal avec pgsql. Il faut bien un début.

J'ai donc créé mes premières fonctions.

voilà le script sql que je teste pour l'instant*

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
 
DROP LANGUAGE IF EXISTS plpgsql CASCADE;
 
CREATE LANGUAGE plpgsql;
 
 
 
------------------------
-- CREATION DES TYPES --
------------------------
DROP TYPE IF EXISTS enum_admingrp CASCADE;
 
CREATE TYPE enum_admingrp AS ENUM ('groupe 1', 'groupe 2');
 
 
 
-------------------------
-- CREATION DES TABLES --
-------------------------
 
DROP TABLE IF EXISTS ADMINISTRATION CASCADE;
 
CREATE TABLE ADMINISTRATION (
	id		serial,
	nom		varchar(64)	NOT NULL UNIQUE CHECK (nom SIMILAR TO '[a-zA-Z -]+' = true),
	adr_postal	varchar(64)	NOT NULL	CHECK (adr_postal SIMILAR TO '([a-zA-Z0-9 ,.-])+' = true),
	cd_postal	numeric(5)	NOT NULL,	CHECK (cd_postal > 1000),
	commune		varchar(32)	NOT NULL,	CHECK (commune SIMILAR TO '[a-zA-Z -]+' = true),
	num_tel		char(10)	NOT NULL,	CHECK (num_tel SIMILAR TO '[[:digit:]]+' = true),
	groupe		enum_admingrp,
 
	CONSTRAINT "administration_pkey" PRIMARY KEY (id)
);
 
----------------------------
-- CREATION DES FONCTIONS --
----------------------------
 
DROP FUNCTION IF EXISTS creer_administration();
 
CREATE FUNCTION creer_administration(_nom varchar, _adr_postal varchar, _cd_postal numeric, _commune varchar, _num_tel char, _groupe enum_admingrp) RETURNS boolean AS
$$
	BEGIN
		INSERT INTO ADMINISTRATION (nom, adr_postal, cd_postal, commune, num_tel, groupe) VALUES (upper(_nom), _adr_postal, _cd_postal, upper(_commune), _num_tel, _groupe);
 
		RAISE NOTICE 'ADMINISTRATION CRÉÉE :%', _nom;
 
		RETURN true;
 
		EXCEPTION
			WHEN check_violation THEN
 
				RAISE NOTICE 'ADMINISTRATION NON CRÉÉE :% (check_violation) ', _nom;
 
				RETURN false;
 
			WHEN not_null_violation  THEN
 
				RAISE NOTICE 'ADMINISTRATION NON CRÉÉE :% (not_null_violation) ', _nom;
 
				RETURN false;
 
			WHEN unique_violation THEN
 
				RAISE NOTICE 'ADMINISTRATION NON CRÉÉE :% (unique_violation) ', _nom;
 
				RETURN false;
	END;
$$
LANGUAGE plpgsql;
 
DROP FUNCTION IF EXISTS creer_organisme();
 
CREATE FUNCTION creer_organisme(_nom varchar, _adr_postal varchar, _cd_postal numeric, _commune varchar, _num_tel char) RETURNS boolean AS
$$
	DECLARE
 
		retour boolean;
 
	BEGIN
		SELECT creer_administration(_nom, _adr_postal, _cd_postal, _commune, _num_tel, 'groupe 1') INTO retour;
 
		RETURN retour;
	END;
$$
LANGUAGE plpgsql;
 
 
 
-----------------------
-- CREATION DES VUES --
-----------------------
 
CREATE VIEW GROUPE1 AS SELECT * FROM ADMINISTRATION WHERE groupe = 'groupe 1';
 
CREATE VIEW GROUPE2 AS SELECT * FROM ADMINISTRATION WHERE groupe = 'groupe 2';
 
 
 
---------------------------
-- INSERTION DES DONNÉES --
---------------------------
 
SELECT creer_administration('organisme un', '9, rue de l eglise', 75000, 'PARIS', '0000000000', 'groupe 1');
 
SELECT creer_organisme('organisme 2', '10, rue de l  eglise', 75000, 'PARIS', '0000000000');
 
SELECT creer_organisme('organisme trois', '9, rue de l''église', 75000, 'PARIS', '0000000000');
 
SELECT creer_organisme('organisme quatre', '9, rue de l eglise', 75000, 'PARIS', '0000000000');
 
SELECT * FROM ADMINISTRATION;

le premier (soucis) c'est que même si je lève des exceptions des id sont créer. Je pensais que en levant des exceptions l'INSERT ne se ferait pas et donc que l'id ne serait pas créé.

Les niveaux possibles sont DEBUG, LOG, INFO, NOTICE, WARNING et EXCEPTION. EXCEPTION lève une erreur (ce qui annule habituellement la transaction en cours)

(sortie de la doc de pgsql 8.3)

Est-ce normale ou y a-t-il un moyen que l'id ne soit pas créer?

Autre question (qui je sais sort un peu du contexte de la question : le CHECK de l'attribut adr_postal ne me permet pas d'avoir des accent 'é' par exemple et je ne permet pas non plus le caractère ' ce qui n'est pas très pratique pour une adresse postale.

j'ai bien essayer de doubler l'entre cote '' ou de mettre des antislash mais rien n'y fait.

Si quelqu'un à la petite astuce pour ça je suis prenneur

Et pour un débutant, je dois avouer que je n'utiliserai plus un SGBD de la même façon à présent.

**SQLpro** · 21/10/2010, 09h42

Ou avez vous appris qu'une exception annule la transaction ? Ce n'est jamais le cas sur aucun SGBDR heureusement !
Le fait d'avoir une exception n'entraîne rien d'autre qu'un traitement particulier branché à l'étiquette d'exception. Après c'est vous gérez...

Si vous voulez annuler la transaction il faut :
1) qu'il existe une transaction explicite
2) que vous invoquiez un ROLLBACK

A +

**gKsam** · 21/10/2010, 10h26

Ou avez vous appris qu'une exception annule la transaction ?

J'ai du mal comprendre la citation que j'ai mis dans le premier poste. Le "habituellement" à l'air de prendre tout son sens içi.

Bon et bien c'est parti pour la transaction explicite et le rollback. Car il me paraissait un peu bizarre d'utiliser un trigger before update et d'y mettre des tests sur les attributs alors qu'à la création de la table j'utilise des check.

Merci pour cette piste à prendre... to be continued...

**gKsam** · 21/10/2010, 15h07

Dans PL/pgSQL, quand une exception est récupérée par une clause EXCEPTION, toutes les modifications de la base de données depuis le bloc BEGIN sont automatiquement annulées

Donc, je crois que je vais devoir utiliser les trigger et vérifier un par un que les valeurs de chaque colonnes soient correct.

**estofilo** · 21/10/2010, 19h47

Est-ce normale ou y a-t-il un moyen que l'id ne soit pas créer?

C'est normal dans la mesure où SERIAL n'est pas un vrai type, ça ne fait que créer une colonne de type INT avec une séquence associée, avec comme valeur par défaut de la colonne la valeur suivante de cette séquence.

Or les séquences sont indépendantes des transactions, donc quand l'INSERT est annulé, la séquence ne retrouve pas sa valeur d'avant, cette valeur est "grillée".

Pour éviter ça, utiliser un type INT avec sa propre technique de calcul de la prochaine valeur, c'est très simple s'il n'y a pas d'insertions concurrentes et un peu plus compliqué s'il y en a.

**estofilo** · 21/10/2010, 19h55

'ai bien essayer de doubler l'entre cote '' ou de mettre des antislash mais rien n'y fait.

L'usage de l'un ou l'autre dépend du paramètre standard_conforming_strings qui est au choix de l'utilisateur. En mode standard on double les apostrophes, dans l'autre mode on met un antislash devant. Dans le cas de l'antislash il faut tout préfixer par E.
Par ailleurs les accents sont autorisés. Exemple:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
test=> select E'ab\'céè' similar to E'[\'a-zéè]+';
 ?column? 
----------
 t
(1 row)

**gKsam** · 22/10/2010, 18h00

Merci pour toutes ces informations

Pour éviter ça, utiliser un type INT avec sa propre technique de calcul de la prochaine valeur, c'est très simple s'il n'y a pas d'insertions concurrentes et un peu plus compliqué s'il y en a.

C'est vrai que sans concurrence un petit max(id) suffit. mais ça ne fonctionnera pas en cas de concurrence... ou tout du moins aussi "bêtement"... je vais y réflechir ce wee-end...

Et merci aussi pour le coups du E et le fait de tester comme ça des SIMILAR TO

Quand je dis à mes supérieurs que c'est un métier la gestion de BDD. Mais j'en avais un peu mare d'utiliser les SGBD comme un "pinpin".

je mets résolu, mais je posterai quand même le résultat de où j'en suis arrivé.

Encore merci.

**gKsam** · 02/11/2010, 14h53

Bon, après avoir lu la doc sur les séquence je crois que je vais laisser faire leurs vies à ces fameux serial vu la valeur max avant que posgresql me sorte une erreur.

2^63-1 comme valeur maximum. ça devrait aller pour pas mal de cas.

Je pense plus qu'il faudrait que je calme ma parano informatique. Ce qui m'inquiétais c'était le fait qu'une machine se mette à me créer des fausses ligne en générant une erreur et qu'elle arrive à bloquer ma table.

id créer avec exception dans fonction

Requêtes PostgreSQL

Discussions similaires

Partager

Partager