postgresql - 如何使用从 string_to_array 到 JOIN 到查找表的数组值

Question

我有一张animals这样的桌子：

      Column      |           Type           | Collation | Nullable | Default 
------------------+--------------------------+-----------+----------+---------
 animal_code      | character varying(3)     |           | not null | 
 animal_type_code | character(2)             |           | not null | 
 description      | character varying(64)    |           | not null | 

典型的内容可能是：

 animal_code | animal_type_code |      description
-------------+------------------+------------------------------
 XAA         | T                | Not an animal, but a toaster
 1           | D                | This is a dog called Bob
 2           | C                | This cat is called Frank
 3           | C                | Wilf the cat has three legs
 4           | D                | Thunder is a dog

我正在使用的现有存储过程接收包含这些animal_code值的逗号分隔列表的文本字符串，如下所示：

store_pet_show_details(
  'London',                      -- p_venue      VARCHAR(64)
  '2019-12-03',                  -- p_date       TIMESTAMPTZ
  'XAA,91,22,23,74,15,64,47,12'  -- p_entrants   TEXT
);

我unnest(string_to_array(code_csv, ','))用来提取动物输入代码。

这可能很简单，但我只是想看看有没有参赛者有animal_type_code“ T”

标签： postgresql

Answer 1

请注意trim.

select animal_code, animal_type_code, description
 from animals
 inner join (select trim(e) entrant from unnest(string_to_array(p_entrants, ',')) e) t
 on animal_code = entrant
 where animal_type_code = 'T';