Hierarchical tag system

gistfile1.sql

CREATE TABLE tag (
  id serial primary key,
  name text not null,
  parent integer references tag(id),
  unique (parent, name)
);

CREATE TABLE photo (
  id serial primary key,
  path text not null
);

CREATE TABLE photo_tag (
  photo integer not null references photo(id),
  tag integer not null references tag(id),
  unique (tag, photo)
);

INSERT INTO tag (id, name, parent)
VALUES (1, 'animal', NULL),
       (2, 'cat', 1),
       (3, 'dog', 1),
       (4, 'hedgehog', 1);

INSERT INTO photo (id, path)
VALUES (1, 'cat.jpg'),
       (2, 'dog.jpg'),
       (3, 'hedgehog.jpg');

INSERT INTO photo_tag (photo, tag)
VALUES (1, 1), -- cat.jpg has tag 'animal' (general)                                                                                                          
       (2, 3), -- dog.jpg has tag 'dog' (specific)                                                                                                            
       (3, 4), -- hedgehog.jpg has tags 'animal' (general) and 'hedgehog' (specific)                                                                          
       (3, 1);

CREATE OR REPLACE FUNCTION resolve_tags(tag text) RETURNS TABLE(id integer) AS
$$
  WITH RECURSIVE r(id, parent) AS (
    SELECT t.id, t.parent FROM tag t WHERE name = $1
    UNION ALL
    SELECT t.id, t.parent FROM r JOIN tag t ON t.parent = r.id
  )
  SELECT id FROM r;
$$
LANGUAGE SQL;

SELECT DISTINCT p.id, p.path
  FROM photo p
  JOIN photo_tag pt ON p.id = pt.photo
 WHERE pt.tag IN (SELECT resolve_tags('animal'));