PostgreSQL函数返回类型不匹配：SETOF posts是否符合预期？

你的函数声明返回SETOF posts不正确，这正是触发"return type mismatch"错误的原因。

错误原因 #

SETOF posts表示函数需要返回与posts表结构完全一致的行集合，但你的查询仅返回了id和distance两个字段，而posts表包含user_id、user_email等多个额外字段，返回字段的数量、类型与声明的返回类型不匹配，因此PostgreSQL抛出类型错误。

修复方案 #

根据你期望返回[{id:..,distance:..}]的需求，提供两种可行方案：

方案1：自定义复合返回类型

先创建一个匹配返回字段的自定义类型，再修改函数返回类型：

-- 创建自定义类型，匹配返回的id和distance字段
CREATE TYPE place_distance AS (
    id bigint,
    distance float
);

-- 重新定义函数，返回SETOF自定义类型
CREATE OR REPLACE FUNCTION near_places(lat float, lng float) 
RETURNS SETOF place_distance AS $$
SELECT
    id,
    distance
FROM 
    (
        SELECT
            id,
            (
                3959 *
                acos(
                    cos(radians($1)) *
                    cos(radians(latitude)) * 
                    cos(radians(longitude) - radians($2)) +
                    sin(radians($1)) *  -- 注意：原代码硬编码了-32.63，这里改为传入的lat参数$1
                    sin(radians(latitude))
                )
            ) AS distance
        FROM posts
    ) p 
WHERE distance < 25
ORDER BY distance LIMIT 20;
$$ LANGUAGE sql;

方案2：直接返回JSON数组（更贴合你的格式需求）

如果希望直接得到[{id:..,distance:..}]格式的结果，可将函数返回类型改为json，用聚合函数生成JSON数组：

CREATE OR REPLACE FUNCTION near_places(lat float, lng float) 
RETURNS json AS $$
SELECT json_agg(json_build_object('id', id, 'distance', distance))
FROM 
    (
        SELECT
            id,
            (
                3959 *
                acos(
                    cos(radians($1)) *
                    cos(radians(latitude)) * 
                    cos(radians(longitude) - radians($2)) +
                    sin(radians($1)) * 
                    sin(radians(latitude))
                )
            ) AS distance
        FROM posts
    ) p 
WHERE distance < 25
ORDER BY distance LIMIT 20;
$$ LANGUAGE sql;

额外提示 #

原函数中存在一个逻辑错误：sin(radians(-32.63))是硬编码的固定值，应该替换为传入的纬度参数$1，否则计算的并非基于输入坐标的距离。

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