如何修改Supabase PostgreSQL函数返回含PlaceCategory实体的Place数据

我在Supabase的SQL编辑器中编写PostgreSQL函数，用于给Swift应用提供数据。现有Place和PlaceCategory两个实体，结构如下：

Place 实体 #

PlaceCategory 实体 #

当前已有一个可正常运行的函数，返回的Place数据中category字段是字符串类型（对应分类的ID）。为了在Swift应用中更便捷地使用查询结果，希望修改函数，让返回的Place数据里的category字段是完整的PlaceCategory实体。现有函数代码如下：

CREATE OR REPLACE FUNCTION get_places_for_city(
    exceptions_param text[],
    categories_param text[],
    price_categories_param text[],
    divisor_param text,
    city_param text
)
RETURNS SETOF public.places AS $test$
BEGIN
    RETURN QUERY
    SELECT
        *
    FROM
        public.places AS place
    WHERE
        place.category = ANY(categories_param)
        AND place.id NOT IN (SELECT unnest(exceptions_param))
        AND place."isActive" IS TRUE
        AND place.city = city_param
        AND (place.price is null OR
        (CASE
            WHEN place.price = 0 THEN 'Free'
            WHEN place.price <= 1500 THEN '$' 
            WHEN place.price <= 3000 THEN '$$'
            ELSE '$$$' 
        END) IN (SELECT unnest(price_categories_param)))
    ORDER BY md5(place.id || divisor_param) ASC
    LIMIT 10;
END;
$test$ LANGUAGE plpgsql;

要实现需求，核心是关联place_categories表，将原Place中的字符串类型category（分类ID）替换为完整的PlaceCategory实体，同时调整函数的返回类型以匹配新的结构。以下提供两种可行方案：

方案1：自定义复合类型（类型安全，推荐用于Swift强类型场景） #

步骤1：创建包含完整分类的复合类型 #

PostgreSQL允许自定义复合类型，我们可以定义一个包含原Place所有字段，且category字段为place_categories表对应类型的新类型：

CREATE TYPE public.PlaceWithFullCategory AS (
    id text,
    name text,
    imageUrl text,
    description text,
    url text,
    infoSource text,
    address text,
    rating double precision,
    price double precision,
    category public.place_categories,
    promotion boolean,
    teamChoose boolean,
    lat double precision,
    lon double precision,
    distance double precision,
    isActive boolean,
    photosArray text[],
    city text,
    keyWords text
);

步骤2：修改函数逻辑 #

关联place_categories表，替换category字段为完整的分类实体，并修改返回类型为新创建的复合类型：

CREATE OR REPLACE FUNCTION get_places_for_city(
    exceptions_param text[],
    categories_param text[],
    price_categories_param text[],
    divisor_param text,
    city_param text
)
RETURNS SETOF public.PlaceWithFullCategory AS $test$
BEGIN
    RETURN QUERY
    SELECT
        place.id,
        place.name,
        place.imageUrl,
        place.description,
        place.url,
        place.infoSource,
        place.address,
        place.rating,
        place.price,
        pc.*::public.place_categories,
        place.promotion,
        place.teamChoose,
        place.lat,
        place.lon,
        place.distance,
        place."isActive",
        place.photosArray,
        place.city,
        place.keyWords
    FROM
        public.places AS place
    JOIN public.place_categories AS pc ON place.category = pc.id
    WHERE
        place.category = ANY(categories_param)
        AND place.id NOT IN (SELECT unnest(exceptions_param))
        AND place."isActive" IS TRUE
        AND place.city = city_param
        AND pc."isActive" IS TRUE -- 可选：仅返回活跃状态的分类
        AND (place.price is null OR
        (CASE
            WHEN place.price = 0 THEN 'Free'
            WHEN place.price <= 1500 THEN '$' 
            WHEN place.price <= 3000 THEN '$$'
            ELSE '$$$' 
        END) IN (SELECT unnest(price_categories_param)))
    ORDER BY md5(place.id || divisor_param) ASC
    LIMIT 10;
END;
$test$ LANGUAGE plpgsql;

优点：类型明确，Swift中可通过Codable直接映射为对应模型，保证类型安全；
缺点：若Place或PlaceCategory表结构变更，需同步更新自定义复合类型。

方案2：返回JSONB格式（灵活，无需维护额外类型） #

如果不想维护自定义类型，可以让函数返回JSONB格式，将PlaceCategory嵌套为JSON对象，Swift端直接解析为嵌套模型：

CREATE OR REPLACE FUNCTION get_places_for_city(
    exceptions_param text[],
    categories_param text[],
    price_categories_param text[],
    divisor_param text,
    city_param text
)
RETURNS SETOF jsonb AS $test$
BEGIN
    RETURN QUERY
    SELECT
        jsonb_build_object(
            'id', place.id,
            'name', place.name,
            'imageUrl', place.imageUrl,
            'description', place.description,
            'url', place.url,
            'infoSource', place.infoSource,
            'address', place.address,
            'rating', place.rating,
            'price', place.price,
            'category', to_jsonb(pc.*),
            'promotion', place.promotion,
            'teamChoose', place.teamChoose,
            'lat', place.lat,
            'lon', place.lon,
            'distance', place.distance,
            'isActive', place."isActive",
            'photosArray', place.photosArray,
            'city', place.city,
            'keyWords', place.keyWords
        )
    FROM
        public.places AS place
    JOIN public.place_categories AS pc ON place.category = pc.id
    WHERE
        place.category = ANY(categories_param)
        AND place.id NOT IN (SELECT unnest(exceptions_param))
        AND place."isActive" IS TRUE
        AND place.city = city_param
        AND pc."isActive" IS TRUE
        AND (place.price is null OR
        (CASE
            WHEN place.price = 0 THEN 'Free'
            WHEN place.price <= 1500 THEN '$' 
            WHEN place.price <= 3000 THEN '$$'
            ELSE '$$$' 
        END) IN (SELECT unnest(price_categories_param)))
    ORDER BY md5(place.id || divisor_param) ASC
    LIMIT 10;
END;
$test$ LANGUAGE plpgsql;

优点：无需维护额外类型，灵活性高，适配表结构变更的成本更低；
缺点：Swift端需处理JSON解析，类型安全依赖解析逻辑的正确性。

内容的提问来源于stack exchange，提问作者Geheiligt