Postgres tableau de recherche dans la clause where

J'ai une requête:

SELECT bar, (SELECT name FROM names WHERE value = bar) as name
FROM foobar WHERE foo = 1 and bar = ANY (1,2,3)

Mon problème c'est que quand il n'y a pas de ligne contenant bar = 3 (ou toute autre valeur est demandé) dans le tableau foobar, aucune ligne n'est retournée pour que la valeur de la barre.

J'aimerais que ma requête pour renvoyer une ligne de [bar, NULL] à la place, mais ne peux pas penser à une façon d'aborder cette.

Est-ce même possible?

Deux choses devraient se préciser votre requête: left outer join permettra de joindre à votre demande. Deuxièmement, les tableaux ne sont pas vraiment faites pour être fouillé comme les mini-tables. Si vous ne connaissez pas l'index du tableau il y a une bonne chance que vous êtes en abuser.
Je pense que le tableau est un hareng rouge ici. Je crois qu'il veut une ligne de (bar, NULL) retourné, ce qui fait sens dans son contexte.
Peter, le tableau est tout simplement une façon de s'adapter à plusieurs valeurs de la barre sans avoir à générer beaucoup de "bar = x ou bar = y ou..." clauses. Je n'ai aucun contrôle dessus de la barre des valeurs, c'est mon entrée et il peut contenir une ou plusieurs valeurs que j'ai à interroger. D'où le tableau. Si il n'y a plus épurée de manière à ce faire avec SQL, j'aimerais vous entendre à ce sujet.
Je pense que votre question et ma réponse serait un meilleur ajustement sur dba.se - si vous l'acceptez, êtes-vous prêt à envisager d'auto-repérage de la migration?

OriginalL'auteur Alex Tokarev | 2011-03-30

  1. 13

    Peut-être quelque chose comme cette approche est ce que vous êtes après:

    banc d'essai:

    create view names as 
select 1 as value, 'Adam' as name union all select 2, 'Beth';

create view foobar as 
select 1 as foo, 1 as bar union all select 1, 2;

    méthode originale:

    select bar, (select name from names where value = bar) as name 
from foobar 
where foo = 1 and bar = any (array[1, 2, 3]);

 bar | name
-----+------
   1 | Adam
   2 | Beth
(2 rows)

    méthode alternative:

    with w as (select unnest(array[1, 2, 3]) as bar)
select bar, (select name from names where value = bar) as name
from w left outer join foobar using(bar);

 bar | name
-----+------
   1 | Adam
   2 | Beth
   3 |
(3 rows)

    Si vous êtes sur 8.3 ou avant, il n'est pas intégré dans le unnest fonction, mais vous pouvez rouler votre propre (pas très efficace) de remplacement:

    create or replace function unnest(anyarray) returns setof anyelement as $$
  select $1[i] from generate_series(array_lower($1,1), array_upper($1,1)) i;
$$ language 'sql' immutable;
    Jack, c'est tout. J'ai déjà trouvé cette magnifique unnest() et la fonction même d'avoir commencé à écrire une réponse à ma propre question, mais vous y êtes arrivé en premier. Merci! 🙂
    Vous êtes les bienvenus - j'ai ajouté un unnest fonction pour n'importe qui qui est sur <8.4 (pas vous bien sûr, comme vous l'avez déjà dit qu'il travaille pour vous)

    OriginalL'auteur

  2. 1
    SELECT bar, name
FROM foobar
INNER JOIN names ON foobar.bar = names.value
WHERE foo = 1 and bar = ANY (1,2,3)

    Essayer cette requête à la place.

    Essayez LEFT JOIN au lieu de OUTER JOIN.
    Merci pour la suggestion, mais ni JOINTURE INTERNE, ni à GAUCHE REJOINDRE fait le tour. LEFT JOIN renvoie toutes les lignes où la barre est définie, et j'ai besoin de retourner une ligne pour chaque valeur requise de la barre, s'il existe dans le foobar table ou pas. C'est mon problème.

    OriginalL'auteur Glen Solsberry

  3. 1
    SELECT  vals.bar, name
FROM    (
        SELECT  *
        FROM    unnest([1, 2, 3]) AS bar
        ) vals
LEFT JOIN
        foobar
ON      foobar.foo = 1
        AND foobar.bar = vals.bar
LEFT JOIN
        names
ON      names.value = vals.bar

    OriginalL'auteur Quassnoi