Forums PostgreSQL.fr

mg

Membre

vue modifiable

Bonjour,

J'essaie de créer une vue modifiable dans ma base postgresql. J'ai déjà créé ma vue à partir de 3 tables (cp_route;cp_troncon_route;cp_troncon_indispo).
J'ai voulu créer une règle  (sur l'insert pour commencer), mais j'ai l'impression que "CREATE RULE" ne supporte pas les conditions dans les instructions. J'ai aussi essayé de passer les instructions dans une fonction "trigger" puis d'appeler la fonction à partir de ma règle, mais ça ne fonctionne pas.
Je mets ci-dessous le contenu de ma règle d'insert. Si vous voyez le problème ou bien si vous connaissez une autre solution... merci

create or replace rule v_cp_troncon_route_i as
on insert to v_cp_troncon_route
do instead (

--Tester l'état de id_gipsi et s'il n'existe pas dans la table CP_ROUTE, créer la route correspondante

IF NEW.id_gipsi IS NULL THEN

     SET var_gipsi = '-1';

ELSIF (NOT EXISTS (SELECT id_gipsi FROM cp_route WHERE id_gipsi=NEW.id_gipsi)) THEN

    INSERT INTO cp_route (id_gipsi,nom,nom_carto,nature,appellations_multiples,code_commune,code_insee,er,dossier_er,quartier1,quartier2,
    motcle01,motcle02,motcle03,motcle04,motcle05,motcle06,motcle07,motcle08,motcle09)
    values (NEW.id_gipsi,NEW.nom,NEW.nature||' '||NEW.nom,NEW.nature,NEW.appellations_multiples,NEW.code_commune,NEW.code_insee,NEW.er,NEW.dossier_er,
    NEW.quartier1,NEW.quartier2,NEW.motcle01,NEW.motcle02,NEW.motcle03,NEW.motcle04,NEW.motcle05,NEW.motcle06,NEW.motcle07,NEW.motcle08,
    NEW.motcle09);
    SET var_gipsi = NEW.id_gipsi;
ELSIF
    SET var_gipsi = NEW.id_gipsi;
END IF;

-- Tester la présence d'un champ indisponibilté rempli et compléter la table cp_troncon_indispo

IF (debut_indisponible IS NOT NULL OR fin_indisponible IS NOT NULL  OR indisponible IS NOT NULL OR motif IS NOT NULL) THEN

    INSERT INTO cp_troncon_indispo (id_troncon,debut_indisponible,fin_indisponible,indisponible,motif)
    values (NEW.id_troncon,NEW.debut_indisponible,NEW.fin_indisponible,NEW.indisponible,NEW.motif);

END IF;

-- Remplir la table CP_TRONCON_ROUTE
INSERT INTO cp_troncon_route (
id_gipsi,
id_troncon,
pid,
classement_administratif,
the_geom,
[...])
values (
var_gipsi,
NEW.id_troncon,
NEW.pid,
NEW.classement_administratif,
NEW.the_geom,
[...]);
);

gleu

Administrateur

Re : vue modifiable

Vous mélangez des requêtes SQL avec un pseudo langage PL/pgsql. Ça ne peut pas fonctionner.

Comme le dit http://docs.postgresqlfr.org/8.3/sql-createrule.html, vous pouvez utiliser une ou plusieurs instructions SQL. Mais IF et SET ne font pas partie du langage SQL.

Il vous reste deux solutions : soit vous exécutez une fonction PL/pgsql à partir de votre règle, soit vous passez par un trigger. De toute façon, dans les deux cas, vous devrez passer par une procédure stockée.

---

Guillaume.

mg

Membre

Re : vue modifiable

J'ai créé la fonction suivante, cependant, je n'arrive pas à la lancer à partir de ma règle -> 'create rule v_troncon_route_i as on insert to v_cp_troncon_route do instead for each row execute procedure insert_troncon()'
Je mélange peut-être encore les deux langages, mais alors je ne vois pas comment lancer une fonction à partir de ma règle.

create or replace function insert_troncon() RETURNS "trigger" AS $insert_troncon$

BEGIN

-- Tester si ID_GIPSI existe dans la table CP_ROUTE

IF var_gipsi IS NULL THEN

     SET var_gipsi = '-1';

ELSIF (NOT EXISTS (SELECT id_gipsi FROM cp_route WHERE id_gipsi=var_gipsi)) THEN

    INSERT INTO cp_route (id_gipsi,nom,nom_carto,nature,appellations_multiples,code_commune,code_insee,er,dossier_er,quartier1,quartier2,
    motcle01,motcle02,motcle03,motcle04,motcle05,motcle06,motcle07,motcle08,motcle09)
    values (var_gipsi,NEW.nom,NEW.nature||' '||NEW.nom,NEW.nature,NEW.appellations_multiples,NEW.code_commune,NEW.code_insee,NEW.er,NEW.dossier_er,
    NEW.quartier1,NEW.quartier2,NEW.motcle01,NEW.motcle02,NEW.motcle03,NEW.motcle04,NEW.motcle05,NEW.motcle06,NEW.motcle07,NEW.motcle08,
    NEW.motcle09);

END IF;

-- Tester la présence d'un champ indisponibilté rempli

IF (debut_indisponible IS NOT NULL OR fin_indisponible IS NOT NULL  OR indisponible IS NOT NULL OR motif IS NOT NULL) THEN

    INSERT INTO cp_troncon_indisponible (id_troncon,debut_indisponible,fin_indisponible,indisponible,motif)
    values (NEW.id_troncon,NEW.debut_indisponible,NEW.fin_indisponible,NEW.indisponible,NEW.motif);

END IF;

-- Remplir la table CP_TRONCON_ROUTE
INSERT INTO cp_troncon_route (
id_gipsi,
id_troncon,
pid,
classement_administratif,
the_geom,
[...])
values (
NEW.id_gipsi,
NEW.id_troncon,
NEW.pid,
NEW.classement_administratif,
NEW.the_geom,
[...]);
RETURN NEW;

END;

$insert_troncon$ LANGUAGE 'plpgsql' VOLATILE;

ALTER FUNCTION insert_troncon() OWNER TO postgres;

gleu

Administrateur

Re : vue modifiable

Cette fois, vous mélangez trigger et règle. L'instruction SQL CREATE RULE ne contient pas de clause « for each row execute procedure ». Ceci serait plus à même de fonctionner :

create rule v_troncon_route_i as on insert to v_cp_troncon_route do instead select insert_troncon()

Autre chose, vu que vous créez une règle, votre fonction ne doit pas renvoyer le type trigger. Elle peut ne rien renvoyer (type void, pas de RETURN dans la fonction).

Pour vos prochains messages, incluez les messages d'erreur, ça évite qu'on ait à les deviner

---

Guillaume.

mg

Membre

Re : vue modifiable

Merci, cela semble fonctionner comme ça.
Par contre, créer une fonction qui ne renvoie rien ne fonctionne pas car la fonction qui ne renvoie pas le type trigger ne reconnaît pas l'état NEW/OLD de l'enregistrement (et ça ne me paraît pas logique de passer les 67 colonnes de ma vue en paramètres) =>

Code :
create or replace function insert_troncon1() RETURNS void AS $insert_troncon$
DECLARE
var_gipsi varchar;
BEGIN
var_gipsi := NEW.id_gipsi;
[...]

Erreur :
ERROR:  NEW used in query that is not in a rule
QUERY:  SELECT  NEW.id_gipsi
CONTEXT:  SQL statement in PL/PgSQL function "insert_troncon1" near line 6

Du coup, j'ai essayé en remplaçant var_gipsi:=NEW.id_gipsi par SET var_gipsi = new.id_gipsi
erreur :
ERROR:  syntax error at or near "$1"
LINE 1: SET  $1  = NEW.id_gipsi
             ^
QUERY:  SET  $1  = NEW.id_gipsi
CONTEXT:  SQL statement in PL/PgSQL function "insert_troncon1" near line 4

gleu

Administrateur

Re : vue modifiable

Ah oui, j'avais oublié ça. Vous ne pouvez pas utiliser NEW directement dans la fonction. Soit vous le fournissez comme argument de la fonction, soit vous fournissez chacune des colonnes (mais avec 67 colonnes, c'est un peu lourd).

L'autre solution est de passer par un trigger directement.

---

Guillaume.

dmboup

Membre

Re : vue modifiable

Bonjour
J'ai une vue sur une table et je voudrais une régle pour la mise à jour de la table mais mon problème c'est la récupération  des données et l'envoi vers la fonction appropriée.

Ci dessous une de mes tentative non fonctionnelles.

CREATE rule mavue_upadate AS ON UPDATE TO une_vue DO INSTEAD SELECT updatemavue_trigger(NEW)

CREATE OR REPLACE FUNCTION updatemavue_trigger(enreg record) RETURNS void AS $$
DECLARE
	un_enreg enreg%ROWTYPE;
	msql varchar :='';
	i integer default 0;
BEGIN
	SELECT * INTO un_enreg FROM matable WHERE id=enreg.id;
	IF FOUND THEN
		
		IF un_enreg.nom <> enreg.nom THEN
			msql := 'nom = ' || enreg.nom;
			i:=1;
		END IF;
		
		IF un_enreg.prenom <> enreg.prenom AND i<>0 THEN
			msql := msql || ' AND prenom=' || enreg.prenom;
		ELSE
			msql := 'prenom=' || enreg.prenom;
			i:=1;
		END IF;
						
		IF i<>0 THEN
			EXECUTE 'UPDATE matable SET ' || msql;
		END IF;
		
	else
		RAISE EXCEPTION 'Update impossible';
	END IF;
END;
$$
LANGUAGE plpgsql;

Dernière modification par dmboup (10/11/2010 18:29:36)

Marc Cousin

Membre

Re : vue modifiable

Bonjour,

Le type de paramètre de la fonction ne peut pas être record. Je présume que c'est votre erreur (mais ça m'aurait facilité le diagnostic que vous le précisiez, merci de le faire la prochaine fois).

CREATE OR REPLACE FUNCTION updatemavue_trigger(enreg une_vue) RETURNS void AS $$

devrait fonctionner… sans présumer évidemment que le reste du code soit valide.

Les tables sont des types valides sous PostgreSQL. Et c'est bien le type de 'NEW'.

Dernière modification par Marc Cousin (10/11/2010 18:13:14)

---

Marc.

dmboup

Membre

Re : vue modifiable

Merci. Mais le problème se situe dans l'écriture de la régle et de la la fonction.

Si dans une application JEE par exemple j'ai un code du genre

Upate ma_vue set champ1='val1', champ2='val2', champ3='val3', ... champn='valn' where id=valeur

comment est ce qu'il faudrait écrire la régle pour faire passer le update que sur les champs qui ont réellement été modifiés? par exemple si seul les champ1, et 3 ont été modifié pouvoir faire

Upate ma_table set champ1='val1', champ3='val3', ... champn='valn' where id=valeur

Dernière modification par dmboup (10/11/2010 18:31:07)

Marc Cousin

Membre

Re : vue modifiable

Vous n'avez qu'une seule façon pour savoir ce qui a changé : comparer tous les champs de NEW avec tous les champs de OLD, qu'il faudra passer en paramètre à votre fonction.

Par ailleurs, à partir du moment où un enregistrement est modifié, mettre à jour un ou 12 champs prendra approximativement le même temps. Donc si c'est juste pour 'ne mettre à jour que ce qui est nécessaire dans les colonnes', ne perdez pas votre temps à ça.

---

Marc.

dmboup

Membre

Re : vue modifiable

Vous n'avez qu'une seule façon pour savoir ce qui a changé : comparer tous les champs de NEW avec tous les champs de OLD

Cest bien ce que je fais dans mais test if. J'ai pas tout présenté ici pas ne pas faire long.

...qu'il faudra passer en paramètre à votre fonction.

Eh bien j'aimerais bien savoir comment faire cela?

Par ailleurs, à partir du moment où un enregistrement est modifié, mettre à jour un ou 12 champs prendra approximativement le même temps. Donc si c'est juste pour 'ne mettre à jour que ce qui est nécessaire dans les colonnes', ne perdez pas votre temps à ça.

Si réellement c'est le même temps entre mettre à jour un champs et 12 alors le update est résolu.

Merci

Dernière modification par dmboup (10/11/2010 18:48:25)

Marc Cousin

Membre

Re : vue modifiable

Juste pour info pour la rule :

CREATE rule mavue_upadate AS ON UPDATE TO une_vue DO INSTEAD SELECT updatemavue_trigger(OLD,NEW)

Évidemment, la déclaration de la fonction change :

CREATE OR REPLACE FUNCTION updatemavue_trigger(old_enreg une_vue, enreg une_vue) RETURNS void AS $$

---

Marc.

dmboup

Membre

Re : vue modifiable

Juste pour info pour la rule :

CREATE rule mavue_upadate AS ON UPDATE TO une_vue DO INSTEAD SELECT updatemavue_trigger(OLD,NEW)

Évidemment, la déclaration de la fonction change :

CREATE OR REPLACE FUNCTION updatemavue_trigger(old_enreg une_vue, enreg une_vue) RETURNS void AS $$

Quels seront les types de old_enreg une_vue, enreg une_vue. et que représente une_vue?

Marc Cousin

Membre

Re : vue modifiable

Le type sera 'une_vue', c'est à dire le type des enregistrements de la table. Toute déclaration d'une table est avant tout la déclaration d'un type d'enregistrement de même nom.

---

Marc.

Marc Cousin

Membre

Re : vue modifiable

Ah oui, j'oubliais, quand je dis une 'table', je veux dire par là, une relation. Cela inclue donc aussi les vues, dont «une_vue»

---

Marc.