Get Category Record By Path in SQL

I have a postgres schema like so:

CREATE TABLE categories 
(
    category_id uuid NOT NULL PRIMARY KEY DEFAULT uuid_generate_v4(),
    parent_id uuid REFERENCES categories(category_id) ON DELETE CASCADE,
    image_url text,
    name text NOT NULL,
    description text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (parent_id, name)
);

This could equally be any SQL language.

What is the general consensus on how to grab a categories record from a path? It seems like this is something people use all the time, but I can’t find one resource on this.

Say motorcyles/wheels/tires from a URL… and I need to grab the category from that information…

I was thinking about writing a crazy SQL function that would return a category_id from a path, but that seems complicated…

Thanks,

J

It seems even wordpress is just doing this in php, so I guess it would be more efficient to write an sql function.

I ended up going with this:

CREATE OR REPLACE FUNCTION get_category_by_path(category_path text)
RETURNS categories AS $$
DECLARE
    path_elements text[];
    current_parent_id uuid := NULL;
    current_record categories;
BEGIN

    path_elements := string_to_array(category_path, "https://stackoverflow.com/");

    FOR i IN 1..array_length(path_elements, 1) LOOP

        -- Grab each category from parent to child
        SELECT * INTO current_record
        FROM categories
        WHERE slug = path_elements[i]
        AND (
          parent_id = current_parent_id
          OR (i = 1 AND parent_id IS NULL)
        );

        IF NOT FOUND THEN
            RAISE EXCEPTION 'No category found for path: %', category_path;
        END IF;

        current_parent_id := current_record.category_id;
    END LOOP;

    RETURN current_record;
END;
$$ LANGUAGE plpgsql;