Forums PostgreSQL.fr

Jiff

Membre

VIEW ch'tit problème

Salut forumers,
.
Existe-t-il un moyen de renvoyer NULL à la place de colonnes inexistantes dans une VIEW?
.
Je m'explique: dans pratiquement chacune de mes tables, j'ai: ri_usr_ins & ri_usr_upd; jusque-là, rien de zarb.

…
  ri_user_ins INTEGER NOT NULL REFERENCES usr(id) ON DELETE RESTRICT,
  ri_user_upd INTEGER NOT NULL REFERENCES usr(id) ON DELETE RESTRICT,
…

Dans ma table des utilisateurs, j'ai pour chacun: ri_firstname & ri_name.

…
  ri_firstname INTEGER NOT NULL REFERENCES name(id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
  ri_name      INTEGER NOT NULL REFERENCES name(id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
…

J'ai une première VIEW qui renvoie: usr(id), usr(firstname) (littéral) & usr(name) (littéral).
   id   | firstname | name
------+----------+------
    1   | JEAN      | TY
.
Une seconde VIEW renvoie les colonnes significatives de la table cible, plus: ins_firstname, ins_name; jusque-là, ça va, mais elle doit aussi renvoyer: upd_firstname & upd_name.
Évidemment, quand le row n'a pas subit d'update, ri_user_upd est NULL.
.
Le problème est que je pensais naïvement que ma 2nde VIEW allait me renvoyer des NULL, mais elle ne renvoie pas les rows n'ayant pas subit d'update.
.
Comment puis-je sortir de cela?  Existe-t-il une possibilité de faire en sorte que des NULL soient renvoyés ou bien d'établir une VIEW conditionnelle qui soit ne renverrait pas les colonnes à NULL, soit les renverrait remplies de NULL?
.
Ou bien dois-je me résoudre à insérer en même temps les mêmes ri_usr_ins & ri_usr_upd dans la table d'origine, afin de ne plus avoir de NULL pour ri_usr_upd?

gleu

Administrateur

Re : VIEW ch'tit problème

Il faudrait donner la définition des vues pour pouvoir vous répondre.

---

Guillaume.

Jiff

Membre

Re : VIEW ch'tit problème

Ok, d'abord les views renvoyant firstname & name pour un tier:

               View "common.vtier_ri_tier_ins"
   Column    |   Type   | Modifiers | Storage  | Description 
-------------+----------+-----------+----------+-------------
 id          | integer  |           | plain    | 
 ri_tier_ins | integer  |           | plain    | 
 firstname   | da_label |           | extended | 
 name        | da_label |           | extended | 
View definition:
 SELECT t.id,
    t.ri_tier_ins,
    vtf.firstname,
    vtn.name
   FROM tier t
     JOIN vtier_firstname vtf ON vtf.id = t.ri_tier_ins
     JOIN vtier_name vtn ON vtn.id = t.ri_tier_ins;

                   View "common.vtier_ri_tier_upd"
   Column    |       Type        | Modifiers | Storage  | Description 
-------------+-------------------+-----------+----------+-------------
 id          | integer           |           | plain    | 
 ri_tier_upd | integer           |           | plain    | 
 firstname   | character varying |           | extended | 
 name        | character varying |           | extended | 
View definition:
 SELECT t.id,
    COALESCE(t.ri_tier_upd, NULL::integer) AS ri_tier_upd,
    COALESCE(vtf.firstname::character varying, NULL::character varying) AS firstname,
    COALESCE(vtn.name::character varying, NULL::character varying) AS name
   FROM tier t
     JOIN vtier_firstname vtf ON vtf.id = t.ri_tier_upd
     JOIN vtier_name vtn ON vtn.id = t.ri_tier_upd;

Celle sur un fichier (en l'occurrence, celui des utilisateurs):

                         View "common.vtier_naminsupd"
    Column     |           Type           | Modifiers | Storage  | Description 
---------------+--------------------------+-----------+----------+-------------
 id            | integer                  |           | plain    | 
 civility      | da_label                 |           | extended | 
 name          | da_label                 |           | extended | 
 login         | character varying(127)   |           | extended | 
 ins_firstname | da_label                 |           | extended | 
 ins_name      | da_label                 |           | extended | 
 ins_date      | dd_tstz                  |           | plain    | 
 upd_firstname | character varying        |           | extended | 
 upd_name      | character varying        |           | extended | 
 upd_date      | timestamp with time zone |           | plain    | 
View definition:
 SELECT t.id,
    c.label AS civility,
    n.label AS name,
    t.login,
    vtif.firstname AS ins_firstname,
    vtin.name AS ins_name,
    t.date_ins AS ins_date,
    COALESCE(vtuf.firstname::character varying, NULL::character varying) AS upd_firstname,
    COALESCE(vtun.name::character varying, NULL::character varying) AS upd_name,
    COALESCE(t.date_upd::timestamp with time zone, NULL::timestamp with time zone) AS upd_date
   FROM tier t
     JOIN civility c ON c.id = t.ri_civility
     JOIN name n ON n.id = t.ri_name
     JOIN vtier_firstname vtif ON vtif.id = t.ri_tier_ins
     JOIN vtier_name vtin ON vtin.id = t.ri_tier_ins
     JOIN vtier_firstname vtuf ON vtuf.id = t.ri_tier_upd
     JOIN vtier_name vtun ON vtun.id = t.ri_tier_upd;

Le contenu actuel de la table common.tier:

SELECT * FROM common.tier;
 id | ri_civility | ri_firstname | ri_name | ri_name_commercial | login |           date_ins            |           date_upd            | ri_tier_ins | ri_tier_upd 
----+-------------+--------------+---------+--------------------+-------+-------------------------------+-------------------------------+-------------+-------------
  1 |           1 |            1 |       2 |                    | admin | 2014-08-30 03:52:56.777909+02 | 2014-09-01 21:38:42.464494+02 |           1 |           1
  3 |           1 |            1 |       2 |                    | test  | 2014-09-01 21:41:12.365147+02 |                               |           1 |            
(2 rows)

Le select sur la view censée tout afficher:

SELECT * FROM common.vtier_naminsupd ;
 id | civility | name | login | ins_firstname | ins_name |           ins_date            | upd_firstname | upd_name |           upd_date            
----+----------+------+-------+---------------+----------+-------------------------------+---------------+----------+-------------------------------
  1 | Mister   | USER | admin | SUPER         | USER     | 2014-08-30 03:52:56.777909+02 | SUPER         | USER     | 2014-09-01 21:38:42.464494+02
(1 row)

Hm, j'ai oublié le résultat des 2 premières views:

SELECT * FROM common.vtier_ri_tier_ins ;
 id | ri_tier_ins | firstname | name 
----+-------------+-----------+------
  1 |           1 | SUPER     | USER
  3 |           1 | SUPER     | USER
(2 rows)

SELECT * FROM common.vtier_ri_tier_upd ;
 id | ri_tier_upd | firstname | name 
----+-------------+-----------+------
  1 |           1 | SUPER     | USER
(1 row)

En fait, ce que je voudrais comme retour, c'est la même chose que le SELECT * FROM common.tier avec noms et prénoms en plus, ce que j'ai pour l'instant uniquement si le row de common.tier a subit un update (avec évidemment des NULL à la place des de firstname & name quand ça n'est pas le cas).
.
Le résultat est logique (puisque vtier_ri_tier_upd ne renvoie qu'un seul row), et c'est certainement évident; mais j'ai le nez dans le guidon depuis trop longtemps pour voir ce qui merdouille
.
Le but final étant d'avoir toujours le même format de retour, et surtout éviter: requête-1, test ri_tier_upd IS NULL?, éventuelle requête-2.
.
Hm, en relisant les docs, je vois une partie de mon erreur (?) et j'ai réécrit 2 views:

CREATE VIEW common.vtier_ri_tier_upd AS
    SELECT  
        T.id,  
        COALESCE(T.ri_tier_upd, 1) AS ri_tier_upd,
        COALESCE(VTF.firstname, '') AS firstname,
        COALESCE(VTN.name, '') AS name
    FROM common.tier T
        INNER JOIN common.vtier_firstname VTF ON VTF.id = ri_tier_upd  
        INNER JOIN common.vtier_name VTN ON VTN.id = ri_tier_upd;

et:

CREATE VIEW common.vtier_naminsupd AS
 SELECT
    t.id,
    c.label AS civility,
    n.label AS name,
    t.login,
    vtif.firstname AS ins_firstname,
    vtin.name AS ins_name,
    t.date_ins AS ins_date,
    COALESCE(t.ri_tier_upd, 1) AS ri_tier_upd,
    COALESCE(vtuf.firstname, '') AS upd_firstname,
    COALESCE(vtun.name, '') AS upd_name,
    COALESCE(t.date_upd, NULL) AS upd_date
   FROM tier t
     JOIN civility c ON c.id = t.ri_civility
     JOIN name n ON n.id = t.ri_name
     JOIN vtier_firstname vtif ON vtif.id = t.ri_tier_ins
     JOIN vtier_name vtin ON vtin.id = t.ri_tier_ins
     JOIN vtier_firstname vtuf ON vtuf.id = ri_tier_upd
     JOIN vtier_name vtun ON vtun.id = ri_tier_upd;

mais ça ne fonctionne toujours pas:(
Déjà, je ne comprends â pourquoi un select sur la Nlle view ne renvoie toujours qu'un seul row au lieu de 2:

SELECT * FROM common.vtier_ri_tier_upd;
 id | ri_tier_upd | firstname | name 
----+-------------+-----------+------
  1 |           1 | SUPER     | USER
(1 row)

puisque COALESCE est censé remplacer ri_tier_upd (NULL) trouvé dans le row #3 de common.tier par 1; donc, je m'attendais à ce que la requête renvoie qq chose comme ça:

SELECT * FROM common.vtier_ri_tier_upd;
 id | ri_tier_upd | firstname | name 
----+-------------+-----------+------
  1 |           1 | SUPER     | USER
  3 |           1 |           |

Dernière modification par Jiff (02/09/2014 01:34:24)

Jiff

Membre

Re : VIEW ch'tit problème

Bon, étant donné que ça urge, j'ai fini par trouver un palliatif.  Pas super génial, mais ça devrait fonctionner comme je le veux.
.
Dans mon 'footer' (où se trouvent les dates ins & upd ainsi que les RIs tier ins & upd), je remplace 'clock_timestamp()' par 'current_timestamp' afin de bloquer le timestamp à l'heure d'appel, et je modifie les defaults de façon à ce que date_ins=date_upd  et ri_tier_upd=1 (soit le SU de la DB); c'est le client qui fera la discrimination par tests d'égalités.
.
Et si ça môrche pô, je sacrifie un développeur java (ça ne manquera à personne;) sur l'autel du DB (Dieu Base).

gleu

Administrateur

Re : VIEW ch'tit problème

Tout d'abord, je ne comprends pas l'utilisation de COALESCE(colonne, NULL). Ça n'a aucun sens pour moi. Autant mettre colonne dans ce cas ? parce que le but de coalesce est de renvoyer une valeur (autre que NULL) si le premier argument est NULL.

Sinon, ça me semble un oubli de LEFT JOIN mais difficile à certifier vu votre message.

---

Guillaume.

Jiff

Membre

Re : VIEW ch'tit problème

Oui, je m'en suis aperçu en relisant attentivement les docs.
J'ai résolu ce PB à la tronçonneuse: comme indiqué plus haut, j'ai maintenant mes date et RI d'update qui sont fixées aux mêmes valeurs que celles d'insertion (le client fera la discrimination entre update "de confort" et réel).  Certainement pas la solution la plus élégante, mais bon, ça roule.
.
<EDIT>
Je viens de faire un test à partir de ton précédent message, parce que je me demandais justement à quoi pouvait servir exactement les LEFT|RIGHT JOINT; ben maintenant je sais, mon problème est définitivement réglé (et d'une façon correcte).
.
Merci encore!

Dernière modification par Jiff (05/09/2014 16:47:35)