#1 Thu 26 July 2018 18:39
- Brice73
- Participant occasionnel
- Date d'inscription: 28 Jun 2018
- Messages: 28
[Postgis] Créer une vue basé sur plusieurs tables
Bonjour
Je voudrais créer des vues contenant les champs de plusieurs tables (reliées entre elles par des clés étrangères).
Le soucis, c'est que les données d'une table ne sont pas forcément liées aux données d'une autre table.
Par exemple, j'ai une table "relevés_oiseaux" et une autre table "commentaire", mais toutes les données d'oiseaux n'ont pas forcément de commentaire.
Dans ma vue, je voudrais afficher les champs de la table "relevés_oiseaux" et de la table "commentaire". Mais en utilisant la clause
WHERE relevés_oiseaux.id = commentaire.relevés_oiseaux_ref_id
la requête ne me retourne que les données d'oiseaux qui comprennent des commentaires. Or je voudrais qu'elle me retourne aussi les données d'oiseaux ne comprenant pas de commentaire.
Comment puis-je faire ?
Merci de votre aide
Hors ligne
#2 Thu 26 July 2018 19:26
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1538
Re: [Postgis] Créer une vue basé sur plusieurs tables
Bonsoir,
Utilisez une jointure (LEFT JOIN) entre les tables:
Code:
select ... from relevés_oiseaux r LEFT JOIN commentaire c on r.id = c.relevés_oiseaux_ref_id
Vous aurez ainsi TOUS les records de la table relevés_oiseaux (sauf si vous ajoutez une clause WHERE), avec une valeur nulle pour les colonnes de la table commentaire pour les lignes qui ne joignent pas.
Je vous invite à ne pas utiliser d'accents dans vos noms de table !
Nicolas
Hors ligne
#3 Thu 26 July 2018 21:07
- Brice73
- Participant occasionnel
- Date d'inscription: 28 Jun 2018
- Messages: 28
Re: [Postgis] Créer une vue basé sur plusieurs tables
Bonsoir
Merci de votre réponse rapide !
En effet, en cherchant, j'avais vu que l'on pouvait faire avec LEFT JOIN.
Le problème, c'est que dans la clause FROM, si j'ai bien compris, une table ne peut être mentionnée qu'une seule fois.
Et ma table "releves_oiseaux" est liée à plusieurs autres tables sur lesquelles j'ai aussi besoin de récupérer des champs pour ma vue.
Donc, comment fait-on pour appliquer LEFT JOIN entre par exemple : table_1 et table_2 ; table_1 et table_3 ; table_2 et table_4 ?
Pour les accents, merci de votre conseil, oui j'ai pris soin de ne pas en mettre dans la base de donnée. C'était juste pour l'exemple.
Brice
Hors ligne
#4 Fri 27 July 2018 09:40
- tumasgiu
- Membre
- Lieu: Ajaccio
- Date d'inscription: 5 Jul 2010
- Messages: 1143
Re: [Postgis] Créer une vue basé sur plusieurs tables
Salut,
rien n'interdit d'inclure plusieurs fois une même table dans une requête.
Il faut juste les distinguer en leur donnant un alias afin que le moteur de base de données
sache s'y retrouver (et la personne qui écrit la requête aussi).
D'autre part votre requête ne nécessite pas de joindre plusieurs fois la table releves_oiseaux.
Une jointure s'applique à l'expression de table qui se trouve à sa gauche.
Ainsi, dans la requête :
Code:
SELECT * FROM releve_oiseau r LEFT JOIN commentaire c ON r.id = c.releve_oiseau_id LEFT JOIN table2 t2 ON r.id = t2.releve_oiseau_id
Le LEFT JOIN table2 ne s'applique pas à la table commentaire mais au resultat de la jointure
releve_oiseau LEFT JOIN commentaire.
On peut écrire la requête de manière non ambiguë, en rajoutant des parenthèses,
(certains moteurs SQL l'imposent d'ailleurs)
Code:
SELECT * FROM ( releve_oiseau r LEFT JOIN commentaire c ON r.id = c.releve_oiseau_id ) LEFT JOIN table2 t2 ON r.id = t2.releve_oiseau_id
Vous pouvez donc chainer vos jointures assez facilement,
il faut juste faire attention au fait que celles ci s'appliquent au résultat
de l'expression de table précédente.
Ainsi si vous restreignez les résultats avec un INNER JOIN dans une première jointure,
ceux ci n'apparaitront pas dans les résultats
et ne seront pas utilisés dans les jointures ultérieures.
Hors ligne
#5 Fri 27 July 2018 12:04
- Brice73
- Participant occasionnel
- Date d'inscription: 28 Jun 2018
- Messages: 28
Re: [Postgis] Créer une vue basé sur plusieurs tables
Bonjour
Ok très bien merci, ça fonctionne !
Autre question : Sur ma vue, j'ai des champs de type booleen. Quand j'ouvre ma vue dans Qgis et que je vais dans la table attributaire, les champs booleen renvoie les valeurs 'f' ou 't'.
Y a t-il un moyen pour que les valeurs affichées soient 'VRAI' ou 'FAUX' ? Faut-il que j'utilise une fonction SI dans la requête SELECT ? Ou y a-t-il un moyen plus simple ?
Brice
Hors ligne
#6 Sat 28 July 2018 11:42
- Mathieu Denat
- Participant actif
- Lieu: Montpellier
- Date d'inscription: 5 May 2010
- Messages: 110
Re: [Postgis] Créer une vue basé sur plusieurs tables
Bonjour,
De mémoire (c'est donc à vérifier) c'est QGIS qui choisit d'afficher un 'f' et 't' dans le champ booléen.
De la même manière qu'il est possible de choisir d'afficher "valeure nulle" au lieu de NULL, il doit être possible de changer cet affichage dans les options de QGIS.
Attention à bien vérifier que le champ concerné est bien interprété comme un champ booléen par Qgis (onglet champs dans les propriétés de la couche). Les champs booléens ne sont pas supportés pour tes les formats, de mémoire ils ne sont supportés que pour les BDD (Spatialite, PostGIS), lors de l'export vers d'autres formats, ils sont convertis en texte.
Enfin, tenter d'insérer du texte dans un champ booléen ne fonctionnera pas en BDD.
Une solution serait de créer un champ texte (à la création de la vue par exemple) et remplir ce champ en utilisant IF ou CASE WHEN (personnellement je n'utilise jamais IF). D'autres pourront dire laquelle des deux méthodes est la moins gourmande en ressources.
J'aurais fait un truc du style:
Code:
SELECT blabla, CASE WHEN champ_booleen IS TRUE THEN 'VRAI'::text WHEN champ_booleen IS FALSE THEN 'FAUX'::text ELSE 'réponse de normand (le champ n'est ni vrai ni faux!)'::text END FROM blabla
Bonne journée,
Mathieu
C'est en forgeant qu'on devient forgeron
Hors ligne
#7 Tue 07 August 2018 14:59
- Brice73
- Participant occasionnel
- Date d'inscription: 28 Jun 2018
- Messages: 28
Re: [Postgis] Créer une vue basé sur plusieurs tables
Bonjour
Oui, en effet, j'ai trouvé dans les options de Qgis comment changer l'affichage des valeurs nulles par contre, je n'ai pas trouvé comment changer l’affichage d'un champ booléen...
Après vérification, Qgis interprète bien le champ concerné comme un type booléen.
En effet, la solution CASE WHEN marche très bien.
J'ai vu que vous aviez mis le type du résultat en text. Savez-vous combien de caractères peut stocker le type text ?
Car sur un autre champ, je stocke des géométries en format EWKT et ces géométries peuvent être assez volumineuses. J'ai mis le champ en type text mais je ne sais pas si cela peut poser problème pour de très grosses entités géométriques.
Merci
Hors ligne
#8 Wed 08 August 2018 10:01
- tumasgiu
- Membre
- Lieu: Ajaccio
- Date d'inscription: 5 Jul 2010
- Messages: 1143
Re: [Postgis] Créer une vue basé sur plusieurs tables
Salut,
il me semblait aussi que l'on pouvait modifier la représentation des valeurs booléennes dans QGIS,
mais je n'ai pas trouvé non plus.
La solution proposée par Mathieu est parfaitement valide, mais c'est quelque-chose
que j'aurais tendance à faire directement dans le visualiseur, ici QGIS, ce afin de dissocier la requête
de la manière dont elle se présente.
Pour votre question sur le type text, la documentation dit que sa taille est illimitée.
Il y a certainement une limite, assez difficile à atteindre je suppose.
Si ce champ EWKT est un champ de table, et pas juste un champ calculée dans une vue,
il faut cependant avoir l'esprit la façon dont Postgres stocke les informations des tables :
Les données d'une même table sont stockées dans une série de fichier ou les lignes s'enregistrent
séquentiellement, ces fichiers sont appelés pages.
La taille par défaut d'une page est de 8KB, et une ligne ne peut pas commencer sur une page et terminer
dans la suivante.
Cette contrainte implique donc qu'une ligne ne peut pas faire plus de 8KB, et que le nombre de page
va exploser si beaucoup de lignes sont volumineuses sans excéder toutefois la limite de 8KB
(Postgres fixe ce critère de taille à 2KB)
Pour contourner cette difficulté, PostgreSQL a deux techniques :
compresser les champs volumineux, ou les stocker dans un fichier à part. Ce mécanisme est appelé TOAST.
SI vos géométries sont vraiment grosses, elles ont des chances de se retrouver dans la TOAST TABLE,
ce qui en soit n'est pas une mauvaise chose, mais ce stockage alternatif induit un coût lors de la récupération
de ces données pendant les requêtes (on appelle cela de-toaster une ligne), qui peut être non négligeable.
Sinon, un petit comparatif sympa sur les différents types de chaines de caractère (en anglais):
https://www.depesz.com/2010/03/02/charx … r-vs-text/
Dernière modification par tumasgiu (Wed 08 August 2018 10:02)
Hors ligne
#9 Wed 08 August 2018 21:11
- Brice73
- Participant occasionnel
- Date d'inscription: 28 Jun 2018
- Messages: 28
Re: [Postgis] Créer une vue basé sur plusieurs tables
Bonjour
Je ne comprends pas bien ce que vous entendez par
mais c'est quelque-chose que j'aurais tendance à faire directement dans le visualiseur
C'est à dire ? comment faire cela depuis Qgis ?
Merci pour vos explications précises sur le type text, je comprends un peu mieux. Et j'avais déjà vu ce fameux terme 'TOAST' dans PgAdmin, je me demandais ce que c'était. J'ai maintenant la réponse
Hors ligne
#10 Wed 08 August 2018 22:56
- tumasgiu
- Membre
- Lieu: Ajaccio
- Date d'inscription: 5 Jul 2010
- Messages: 1143
Re: [Postgis] Créer une vue basé sur plusieurs tables
Je voulais dire que j'aurai créé des colonnes calculées dans QGIS pour gérer
la représentation textuelle des colonnes booléennes, pour dissocier application
et présentation.
Mais j'avoue, c'est du chipotage de développeur.
Dernière modification par tumasgiu (Wed 08 August 2018 23:07)
Hors ligne
#11 Thu 09 August 2018 09:53
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1538
Re: [Postgis] Créer une vue basé sur plusieurs tables
Bonjour,
Puis-je vous demander pkoi vous stockez les geom au format texte ?
Ca prend tellement de place: si vous avez deja la geom en geometry, mieux vaut appeler la représentation text avec st_astext(geom) quand vous en avez besoin.
Nicolas
Hors ligne
#12 Mon 13 August 2018 08:43
- JD
- Moderateur
- Date d'inscription: 8 Aug 2013
- Messages: 726
Re: [Postgis] Créer une vue basé sur plusieurs tables
Bonjour,
il me semblait aussi que l'on pouvait modifier la représentation des valeurs booléennes dans QGIS,
mais je n'ai pas trouvé non plus.
C'est à dire ? comment faire cela depuis Qgis ?
Propriété de la couche > Champs > Cliquer sur Edition de texte et choisir Boite à cocher
et dans représentation d'un état coché mettre VRAI et représentation d'un état décoche mettre FAUX.
Cordialement,
Dernière modification par lejedi76 (Mon 13 August 2018 08:45)
Hors ligne
#13 Tue 14 August 2018 10:54
- Brice73
- Participant occasionnel
- Date d'inscription: 28 Jun 2018
- Messages: 28
Re: [Postgis] Créer une vue basé sur plusieurs tables
Bonjour
Merci pour vos réponses.
Tumasgiu, si je créé des colonnes calculés dans Qgis, le soucis, c'est qu'il faut les recréer à chaque fois que l'on rouvre la vue dans Qgis. Ou alors, il y a un moyen qu'elles se créent automatiquement ?
Nicolas, les données récupérées dans la BDD sont issues d'une application Android qui stocke toutes les données (points, lignes et polygones) sous forme de simple coordonnées GPS. Donc passer par le format EWKT me paraissait être la chose la plus simple pour passer des coordonnées GPS au type geometry de Postgis.
Lejedi76, en effet la solution de changer l'outil d'édition dans les propriétés de la couche fonctionne mais la modification de l'outil d'édition n'est pris en compte que lorsque l'on édite les données. Donc après avoir fait la modification que tu as décrite, tant que l'on ouvre juste la table attributaire sans modifier les attributs en question, les valeurs affichées restent 't' et 'f'...
Autre question : J'ai une vue affichant les données de 3 tables : table oiseaux, table point et table ligne. Sachant que des données d'oiseaux peuvent être liés à un point et une ligne ou seulement à un point.
Comment n'afficher dans la vue que les données liés à un point mais n'étant pas liés à une ligne ?
Concrètement, ce que je n'arrive pas à faire, c'est demander d'afficher les données de ma table1 ne devant pas avoir de lien avec la table2.
Hors ligne
#14 Tue 14 August 2018 14:00
- tumasgiu
- Membre
- Lieu: Ajaccio
- Date d'inscription: 5 Jul 2010
- Messages: 1143
Re: [Postgis] Créer une vue basé sur plusieurs tables
Bonjour
Tumasgiu, si je créé des colonnes calculés dans Qgis, le soucis, c'est qu'il faut les recréer à chaque fois que l'on rouvre la vue dans Qgis. Ou alors, il y a un moyen qu'elles se créent automatiquement ?
Oui si vous ouvrez votre vue dans plusieurs projets QGIS, il vous faudra à chaque fois recréer ces colonnes.
Toutefois, je ne suis pas un pro de QGIS, mais je crois que si vous enregistrez la définition de la couche, vous avez la possibilité
d'enregistrer aussi les colonnes virtuelles.
Une fois enregistrée, vous utilisez le ficier qlr pour charger votre vue dans votre projet au lieu de charger la couche postgis.
Autre question : J'ai une vue affichant les données de 3 tables : table oiseaux, table point et table ligne. Sachant que des données d'oiseaux peuvent être liés à un point et une ligne ou seulement à un point.
Comment n'afficher dans la vue que les données liés à un point mais n'étant pas liés à une ligne ?
Concrètement, ce que je n'arrive pas à faire, c'est demander d'afficher les données de ma table1 ne devant pas avoir de lien avec la table2.
Récapitulatif :
a INNER JOIN b ON p
-> renvoie toutes les combinaison de la table a X la table b quand le prédicat p est vrai.
a OUTER LEFT JOIN b ON p
->
renvoie toutes les combinaison de la table a X la table b quand le prédicat p est vrai
ainsi que les lignes de a n'ayant pas satisfait p;
Pour ces lignes de a qui n'ont pas été associées à b,
les champs de b sélectionnées dans la clause SELECT valent tous NULL
a OUTER RIGHT JOIN b ON p
->
idem que la jointure précédente sauf que ce sont les lignes de b n'ayant pas satisfait p qui sont renvoyées.
a FULL OUTER JOIN b ON p
-> équivaut à LEFT JOIN b ON p UNION a RIGHT JOIN b ON p
Dernière modification par tumasgiu (Tue 14 August 2018 14:02)
Hors ligne
#15 Wed 22 August 2018 20:03
- Brice73
- Participant occasionnel
- Date d'inscription: 28 Jun 2018
- Messages: 28
Re: [Postgis] Créer une vue basé sur plusieurs tables
Toutefois, je ne suis pas un pro de QGIS, mais je crois que si vous enregistrez la définition de la couche, vous avez la possibilité
d'enregistrer aussi les colonnes virtuelles.
Une fois enregistrée, vous utilisez le ficier qlr pour charger votre vue dans votre projet au lieu de charger la couche postgis.
Ok merci. Je ne m'étais jamais intéressé à ce type de fichier. J'ignorais ce qu'étais un fichier de définition de couche. Et, en effet, ça a l'air très pratique !
Merci pour le rappel des fonctions JOIN. Je pense maintenant avoir bien compris leur fonctionnement.
Mais ce que je cherche à faire est l'inverse de INNER JOIN. C'est à dire de renvoyer toutes les combinaison de la table a X la table b quand le prédicat p n'est pas vrai (j'applique le prédicat sur les champs permettant de joindre les 2 tables). J'ai déjà essayé d'utiliser l'opérateur : '<>', mais quand j'actualise les données de la vues, ça se met à ramer... Donc je pense que ce n'est pas bonne solution.
Pour l'instant, je fais autrement, j'ai créé 2 vues différentes.
Hors ligne
#16 Wed 22 August 2018 20:45
- tumasgiu
- Membre
- Lieu: Ajaccio
- Date d'inscription: 5 Jul 2010
- Messages: 1143
Re: [Postgis] Créer une vue basé sur plusieurs tables
J'ai déjà essayé d'utiliser l'opérateur : '<>', mais quand j'actualise les données de la vues, ça se met à ramer... Donc je pense que ce n'est pas bonne solution.
Effectivement ca n'est pas la bonne solution, la requête rame car il y a explosion combinatoire, ce que vous faites
revient presque faire un CROSS JOIN privé des lignes qui concordent.
Ce n'est pas les prédicat de jointure qu'il faudrait changer, mais autre chose.
Hors ligne
#17 Thu 23 August 2018 00:11
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1538
Re: [Postgis] Créer une vue basé sur plusieurs tables
Bonsoir,
Pour matérialiser les conditions négatives, une solution efficace sont les anti join:
Code:
select .... from table t where not exists ( select null from t1 where <condition vraie pour t et t1> )
Nicolas
Hors ligne
#18 Tue 23 October 2018 18:37
- Brice73
- Participant occasionnel
- Date d'inscription: 28 Jun 2018
- Messages: 28
Re: [Postgis] Créer une vue basé sur plusieurs tables
Merci pour votre réponse Nicolas, je ne savais pas que l'on pouvait faire des anti join.
Je suis maintenant sur la problématique d'édition des vues. Pour pouvoir mettre à jour les vues, j'ai créé des triggers. Mais pour écrire les requêtes de mises à jour des vues, je rencontre le problème suivant :
J'ai une table "de référence" (nommée "taxref"), complétée avec des données officielles qu'il ne faut pas que je modifie. Dans cette table, j'ai par exemple, les champs "cd_nom" (qui est la clé primaire) et "nom_scien".
J'ai une autre table ("table_2") contenant des données que je peux modifier et qui fait référence à la table "taxref" sur sa clé primaire. J'ai par exemple les champs "caracteristique", "nombre", "cd_nom_ref_id" (clé étrangère faisant référence à la table "taxref")
Sur ma vue, j'affiche les champs de "table_2" et le champ "nom_scien" de "taxref".
Comment écrire dans ma requête update, que quand une valeur du champ "nom_scien" est modifiée, il faut modifier la valeur dans le champ "cd_nom_ref_id" en prenant (dans la table taxref) la valeur du champ "cd_nom" correspondante à la valeur mise à jour (qui doit forcément être une valeur déjà contenue dans la table "taxref") ?
Je ne sais pas non plus comment faire pour les requête create et delete...
Hors ligne
#19 Wed 24 October 2018 10:06
- tumasgiu
- Membre
- Lieu: Ajaccio
- Date d'inscription: 5 Jul 2010
- Messages: 1143
Re: [Postgis] Créer une vue basé sur plusieurs tables
Utilisez le paramètre INSTEAD OF de votre trigger
pour rediriger les instructions des requêtes effectuées sur
votre vue vers table_2.
Dans votre trigger, à l'aide des deux variables spéciales crées automatiquement
dans tout trigger, NEW et OLD,
qui symbolisent l'état de l'enregistrement en cours de mise à jour
respectivement après et avant exécution de la requête,
comparez si la colonne nom_scien a changé.
Si c'est le cas, récupérez la valeur cd_nom associée
dans la table taxref, et mettez à jour cd_nom_ref_id en conséquence.
Sinon, question bête, pourquoi ne pas utiliser nom_scien pour faire la jointure
dans votre vue ? Cela vous simplifierait grandement l’écriture du trigger.
Dernière modification par tumasgiu (Wed 24 October 2018 10:12)
Hors ligne
#20 Wed 24 October 2018 10:26
- tumasgiu
- Membre
- Lieu: Ajaccio
- Date d'inscription: 5 Jul 2010
- Messages: 1143
Re: [Postgis] Créer une vue basé sur plusieurs tables
Maintenant j'attends le post suivant qui va dire
que çà n'est pas une bonne idée d'utiliser une clef naturelle,
de surcroit textuelle, comme clef primaire
Hors ligne
#21 Wed 24 October 2018 10:39
- tumasgiu
- Membre
- Lieu: Ajaccio
- Date d'inscription: 5 Jul 2010
- Messages: 1143
Re: [Postgis] Créer une vue basé sur plusieurs tables
Bonsoir,
Pour matérialiser les conditions négatives, une solution efficace sont les anti join:Code:
select .... from table t where not exists ( select null from t1 where <condition vraie pour t et t1> )Nicolas
Merci pour votre réponse Nicolas, je ne savais pas que l'on pouvait faire des anti join.
Juste pour info,
Une autre façon de réaliser un anti join, en rapport avec mon laïus d'Aout sur les JOIN:
Code:
SELECT t1.* FROM t1 LEFT INNER JOIN t2 ON t1.col = t2.col WHERE t2.col is NULL
Dernière modification par tumasgiu (Wed 24 October 2018 10:40)
Hors ligne
#22 Wed 24 October 2018 11:54
- ChristopheV
- Membre
- Lieu: Ajaccio
- Date d'inscription: 7 Sep 2005
- Messages: 3178
- Site web
Re: [Postgis] Créer une vue basé sur plusieurs tables
Bonjour,
Maintenant j'attends le post suivant qui va dire
que çà n'est pas une bonne idée d'utiliser une clef naturelle,
de surcroit textuelle, comme clef primaire
smile
Ben non, les règles sont faites pour avoir des exceptions
Christophe
L'avantage d'être une île c'est d'être une terre topologiquement close
Hors ligne
#23 Wed 24 October 2018 13:02
- Brice73
- Participant occasionnel
- Date d'inscription: 28 Jun 2018
- Messages: 28
Re: [Postgis] Créer une vue basé sur plusieurs tables
Ok merci
Sinon, question bête, pourquoi ne pas utiliser nom_scien pour faire la jointure dans votre vue ? Cela vous simplifierait grandement l’écriture du trigger.
Car plusieurs vues utilisent cette table mais certaines affichent un champ différent.
Par exemple, vue_1 va afficher taxref.nom_scien mais vue_2 va afficher taxref.nom_vern et quelque soit le champ, les attributs doivent pouvoir être modifiés.
Dans votre trigger, à l'aide des deux variables spéciales crées automatiquement dans tout trigger, NEW et OLD, qui symbolisent l'état de l'enregistrement en cours de mise à jour respectivement après et avant exécution de la requête, comparez si la colonne nom_scien a changé.
Si c'est le cas, récupérez la valeur cd_nom associée dans la table taxref, et mettez à jour cd_nom_ref_id en conséquence.
Comment écrire cela en SQL ? Dans la fonction trigger, j'ai essayé comme ci-dessous, mais ça ne fonctionne pas :
Code:
....... UPDATE table_2 SET caracteristique = new.caracteristique, nombre = new.nombre, CASE WHEN nom_scien = new.nom_scien THEN taxref.cd_nom where new.nom_scien = taxref.nom_scien ELSE ''::text END ......
Mais déjà, je ne sais si utiliser CASE WHEN est la bonne solution et ensuite, je ne suis pas sûr de pouvoir mettre un WHERE dans un CASE WHEN.
Hors ligne
#24 Wed 24 October 2018 14:12
- tumasgiu
- Membre
- Lieu: Ajaccio
- Date d'inscription: 5 Jul 2010
- Messages: 1143
Re: [Postgis] Créer une vue basé sur plusieurs tables
Quelque chose comme çà (fautes de syntaxe incluses):
CREATE OR REPLACE FUNCTION upd_view() RETURNS TRIGGER AS
$$
DECLARE
new_nom TEXT; --variable servant à stocker la nouvelle valeur cd_nom
BEGIN
IF OLD.nom_scien <> NEW.nom_scien THEN
SELECT cd_nom INTO new_nom FROM taxref WHERE nom_scien = NEW.nom_scien
IF new_nom IS NULL THEN
-- si aucun nom scientifique ne correspond, on ne met rien à jour.
RAISE EXCEPTION 'Nom scientifique incorrect : %', NEW.nom_scien;
END IF;
UPDATE table_2 SET cd_nom = new_nom WHERE id_table_2 = NEW.id_table_2;
END IF;
END;
$$ LANGUAGE plpgsql
Dernière modification par tumasgiu (Wed 24 October 2018 14:14)
Hors ligne
#25 Thu 25 October 2018 20:01
- Brice73
- Participant occasionnel
- Date d'inscription: 28 Jun 2018
- Messages: 28
Re: [Postgis] Créer une vue basé sur plusieurs tables
Ok merci.
Alors le soucis, c'est que dans mes vues, je n'affiche pas les clés primaires des tables, mais je génère un identifiant unique avec row_number() OVER ()::integer
Et étant donné, qu'il n'y a pas de corrélation entre l'id de ma table et l'id générée sur ma vue, je n'arrive pas à trouver quels champs il faudrait que je mette dans le WHERE de l'UPDATE de table_2... pour que le trigger mette à jour uniquement la ligne modifiée.
Autre question à laquelle je n'arrive pas à trouver de réponse (même en ayant passé quelques heures à chercher sur internet) : dans une fonction trigger, avant le END; final de la requête, apparemment, il faut mettre RETURN 'new', 'old', 'null' ou un autre attribut. Savez-vous à quoi sert ce RETURN ?
Hors ligne
#26 Fri 26 October 2018 00:27
- tumasgiu
- Membre
- Lieu: Ajaccio
- Date d'inscription: 5 Jul 2010
- Messages: 1143
Re: [Postgis] Créer une vue basé sur plusieurs tables
Alors le soucis, c'est que dans mes vues, je n'affiche pas les clés primaires des tables, mais je génère un identifiant unique avec row_number() OVER ()::integer
Et étant donné, qu'il n'y a pas de corrélation entre l'id de ma table et l'id générée sur ma vue, je n'arrive pas à trouver quels champs il faudrait que je mette dans le WHERE de l'UPDATE de table_2... pour que le trigger mette à jour uniquement la ligne modifiée.
De ce que j'ai compris, vous joignez à votre table table_2 des informations relative à l'espece d'oiseau concernée par chacune des lignes ?
Si c'est le cas, votre vue comportera exactement une fois chaque ligne de table_2.
Dans ce cas pourquoi ne pas uitiliser l'identifiant de table_2 à la place de row_number ?
Code:
Autre question à laquelle je n'arrive pas à trouver de réponse (même en ayant passé quelques heures à chercher sur internet) : dans une fonction trigger, avant le END; final de la requête, apparemment, il faut mettre RETURN 'new', 'old', 'null' ou un autre attribut. Savez-vous à quoi sert ce RETURN ?
Oubli de ma part.
RETURN est l'instruction PL/PGSQL qui détermine quelle valeur sera retournée par la fonction,
ce qui est un des concepts de base de l'algorithmique.
Ici en l’occurrence la fonction doit retourner un objet du type event_trigger.
Les variables OLD et NEW sont de ce type
On peut se référer à la documentation de postgresql pour savoir quelle valeur renvoyer.
Dans le cas d'un trigger INSTEAD OF, la seule rêgle est que NULL doit être renvoyé si
aucune mise à jour n'est effectuée, NEW pour les instructions INSERT & DELETE,
et OLD, pour DELETE.
cf. https://doc.postgresql.fr/10/plpgsql-trigger.html
Pour approfondir:
https://doc.postgresql.fr/10/plpgsql.htm
La doc officielle est votre amie, même si elle est un peu austère.
Dernière modification par tumasgiu (Fri 26 October 2018 15:51)
Hors ligne
#27 Mon 29 October 2018 10:49
- BenB
- Participant actif
- Date d'inscription: 29 Dec 2015
- Messages: 120
Re: [Postgis] Créer une vue basé sur plusieurs tables
Bonjour Brice 73,
Je travaille exactement sur la même problématique : des écologues vont sur le terrains faire des relevées faune flore (application ObsMap sur smartphone), à leur retour j'intègre leur données (fichier .csv) dans un table observation. (Postgis).
je créé des vues pour faire la jointure entre mes tables observations et les tables de l'inpn (taxref, et liste rouges) avec CD_NOM pour clef unique.
Par contre je n'ai pas fait de vues modifiables, pour l'instant quand ils ont besoin de modifier ou d'ajouter des observations ils le font directement sur la table sous QGIS. j'ai fait des attributs virtuels qui complètent avec nom_vernaculaire et nom_scientifique la table attributaire.
Je sais c'est pas très secur mais j'ai verrouillé au maximum la saisie avec des NOT NULL, des listes déroulantes, et en cachant des champs avec un formulaire de saisie.
Les vues leur permettent de faire les cartes et les analyses thématiques.
Si tu veux on peut échanger par MP, pour un retour d'expérience.
Hors ligne
#28 Tue 30 October 2018 15:48
- Brice73
- Participant occasionnel
- Date d'inscription: 28 Jun 2018
- Messages: 28
Re: [Postgis] Créer une vue basé sur plusieurs tables
Merci pour votre réponse précise sur l'instruction RETURN
De ce que j'ai compris, vous joignez à votre table table_2 des informations relative à l'espece d'oiseau concernée par chacune des lignes ?
Si c'est le cas, votre vue comportera exactement une fois chaque ligne de table_2.
Dans ce cas pourquoi ne pas uitiliser l'identifiant de table_2 à la place de row_number ?
Oui c'est bien cela, sauf que table_2 est aussi lié à une table contenant la géométrie des entités. Et une ligne de table_2 peut être liée à plusieurs entités géométriques donc à plusieurs lignes de la table contenant la géométrie.
Donc si je met l'id de la table_2 à la place de row_number dans la vue, cet id ne sera pas unique.
BenB, je suis d'accord, ça peut être super intéressant !
Hors ligne
#29 Tue 30 October 2018 19:13
- Brice73
- Participant occasionnel
- Date d'inscription: 28 Jun 2018
- Messages: 28
Re: [Postgis] Créer une vue basé sur plusieurs tables
tumasgiu, J'ai affiché sur la vue les id des tables que je souhaite modifier (table_2 et table_geometrie), sous forme de champ sans que je ne les considère comme identifiant unique sur Qgis. Je m'en sert donc dans le WHERE de l'UPDATE pour la requête de modification et ça a l'air de fonctionner.
Hors ligne
#30 Wed 31 October 2018 16:14
- tumasgiu
- Membre
- Lieu: Ajaccio
- Date d'inscription: 5 Jul 2010
- Messages: 1143
Re: [Postgis] Créer une vue basé sur plusieurs tables
Si tu veux on peut échanger par MP, pour un retour d'expérience.
BenB, je suis d'accord, ça peut être super intéressant !
Ah oui, c'est intéressant en effet, pourquoi ne pas échanger sur un fil séparé plutôt ?
Ceux ci pourraient peut être aider d'autres personnes à faire les bons choix.
Ou rédiger un petit article après vos échanges et le poster je sais pas.
Enfin, simple suggestion.
tumasgiu, J'ai affiché sur la vue les id des tables que je souhaite modifier (table_2 et table_geometrie), sous forme de champ sans que je ne les considère comme identifiant unique sur Qgis. Je m'en sert donc dans le WHERE de l'UPDATE pour la requête de modification et ça a l'air de fonctionner.
Oui, je pense que çà devrait fonctionner, sans trop de problème.
Je vous conseille de faire tout de même quelques test avant de passer en production.
Hors ligne