How to use array values from string_to_array to JOIN to a look-up table

Question

I have an animals table like this:

      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 |

Typical content might be:

 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

An existing stored procedure I'm working with receives a text string containing a comma-separated list of these animal_code values, like this:

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
);

I'm using unnest(string_to_array(code_csv, ',')) to extract the animal entry codes.

It's probably very simple, but I just want to see if any entrants have an animal_type_code of "T"

Stefanov.sm · Accepted Answer · 2021-05-28 09:30:29Z

1

Please note that 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';

answered May 28, 2021 at 9:30

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use array values from string_to_array to JOIN to a look-up table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related