PostGIS Postgres函数与SQL查询返回结果不一致问题排查

我有一个存储城市地理编码（如lat: 55.8652, lon: -4.2514）的数据库。在Supabase SQL编辑器中执行以下SQL语句可得到预期结果：

SELECT *
FROM education
WHERE ST_Distance (education.loc, ST_SetSRID (ST_MakePoint ( -4.2514, 55.8652), 4326)) <= 8000
order by
ST_Distance (
education.loc,
ST_SetSRID (ST_MakePoint ( -4.2514, 55.8652), 4326)
)
LIMIT 10;

但将该逻辑封装为Postgres函数后，在Flutter应用中调用时无法得到预期结果，仅返回education表的前10行。函数代码如下：

create
or replace function "education" ( lat float, lon float) returns setof education language sql as $$
SELECT *
FROM education
WHERE ST_Distance (education.loc, ST_SetSRID (ST_MakePoint (lon, lat), 4326)) <= 8000
order by
ST_Distance (
education.loc,
ST_SetSRID (ST_MakePoint (lon, lat), 4326)
)
LIMIT 10;
$$;

我检查后发现函数的lat和lon参数类型为double precision，怀疑这是问题所在。尝试将参数改为numeric并在ST_MakePoint中转换为float，但仍无效，目前陷入困惑。以下是Flutter中的调用代码：

const a = await supabase.rpc("education_search", {
  page: 0,
  lat: 55.8652,
  lon: -4.2514,
});

console.log(a);

1. 修正函数名称不匹配问题 #

你在Flutter中调用的RPC函数名是education_search，但定义的函数名是"education"，这会导致调用的不是你编写的地理查询逻辑，而是默认返回表数据的逻辑。先将函数名改为和调用时一致：

create or replace function "education_search" (lat float, lon float) 
returns setof education 
language sql as $$
SELECT *
FROM education
WHERE ST_Distance(education.loc, ST_SetSRID(ST_MakePoint(lon, lat), 4326)) <= 8000
ORDER BY ST_Distance(education.loc, ST_SetSRID(ST_MakePoint(lon, lat), 4326))
LIMIT 10;
$$;

2. 确认参数类型兼容性 #

ST_MakePoint接受double precision类型参数，Postgres中float就是double precision的别名，所以参数类型不是核心问题。若要明确标注，可直接使用double precision：

create or replace function "education_search" (lat double precision, lon double precision) 
returns setof education 
language sql as $$
SELECT *
FROM education
WHERE ST_Distance(education.loc, ST_SetSRID(ST_MakePoint(lon, lat), 4326)) <= 8000
ORDER BY ST_Distance(education.loc, ST_SetSRID(ST_MakePoint(lon, lat), 4326))
LIMIT 10;
$$;

3. 优化查询性能（可选） #

直接使用ST_Distance会触发全表扫描，建议改用ST_DWithin配合空间索引提升效率，同时避免重复计算距离：

create or replace function "education_search" (lat double precision, lon double precision) 
returns setof education 
language sql as $$
SELECT e.*, ST_Distance(e.loc, ST_SetSRID(ST_MakePoint(lon, lat), 4326)) AS distance
FROM education e
WHERE ST_DWithin(e.loc, ST_SetSRID(ST_MakePoint(lon, lat), 4326), 8000)
ORDER BY distance
LIMIT 10;
$$;

如果还未创建空间索引，先给loc字段添加：

CREATE INDEX idx_education_loc ON education USING GIST (loc);

4. 修正Flutter调用参数 #

你的函数定义中没有page参数，调用时需移除该无关参数：

final a = await supabase.rpc("education_search", {
  'lat': 55.8652,
  'lon': -4.2514,
});

print(a);

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