Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE all_animals(id int, animals text);
INSERT INTO all_animals VALUES (100, 'Cat,Dog,Bird');

CREATE TABLE

INSERT 0 1

SELECT quote_literal(unnest(string_to_array(animals, ','))) AS pets
FROM   all_animals
WHERE  id = 100;

pets

'Cat'

'Dog'

'Bird'

SELECT 3

WITH a AS (
   SELECT unnest(string_to_array(animals, ',')) AS pets
   FROM   all_animals
   WHERE  id = 100
   )
SELECT quote_literal(pets) AS pets
FROM   a;

pets

'Cat'

'Dog'

'Bird'

SELECT 3