Nous utilisons des cookies pour vous garantir la meilleure expérience sur notre site. Si vous continuez à utiliser ce dernier, nous considèrerons que vous acceptez l'utilisation des cookies. J'ai compris ! ou En savoir plus !.
banniere

Le portail francophone de la géomatique


Toujours pas inscrit ? Mot de passe oublié ?
Nom d'utilisateur    Mot de passe              Toujours pas inscrit ?   Mot de passe oublié ?

Annonce

Suite à un problème technique intervenu entre le 22 et le 23 mars, nous avons du procéder dans la soirée du 25 mars, à la restauration de la base de données du 24 mars (matinée).

En clair, nous avons perdu vos contributions et inscriptions du dimanche 24 et du lundi 25 mars.
Nous vous prions de nous excuser.

#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: 1534

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: 1129

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: 1129

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 smile

Hors ligne

 

#10 Wed 08 August 2018 22:56

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1129

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: 1534

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: 722

Re: [Postgis] Créer une vue basé sur plusieurs tables

Bonjour,

tumasgiu a écrit:

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.


Brice73 a écrit:

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: 1129

Re: [Postgis] Créer une vue basé sur plusieurs tables

Brice73 a écrit:

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.


Brice73 a écrit:

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: 1129

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: 1534

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: 1129

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: 1129

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
smile

Hors ligne

 

#21 Wed 24 October 2018 10:39

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1129

Re: [Postgis] Créer une vue basé sur plusieurs tables

Nicolas Ribot a écrit:

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


Brice73 a écrit:

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: 3163
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 wink


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: 1129

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: 1129

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: 1129

Re: [Postgis] Créer une vue basé sur plusieurs tables

BenB a écrit:

Si tu veux on peut échanger par MP, pour un retour d'expérience.


Brice73 a écrit:

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.

Brice73 a écrit:

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

 

Pied de page des forums

Powered by FluxBB