如何使用连接返回结果?
问题描述:
现在我有以下功能:如何使用连接返回结果?
CREATE OR REPLACE FUNCTION set_city(_city_id bigint, _country_id integer, _lat float, lon float) RETURNS geo_cities LANGUAGE plpgsql as $$
DECLARE
city_coords Geometry := ST_SetSrid(ST_MakePoint(_lon, _lat), 3395);
result record;
BEGIN
IF EXISTS (SELECT 1 FROM geo_cities gc WHERE gc.id = _city_id)
THEN
UPDATE geo_cities
SET coords = city_coords, country_id = _country_id
WHERE id = _city_id
RETURNING * INTO result;
ELSE
INSERT INTO geo_cities(id, country_id, coords)
VALUES (_city_id, _country_id, city_coords)
RETURNING * INTO result;
END IF;
RETURN result;
END;
$$
我想用我的结果加入。这就是我的意思:
...
RETURNING
id as city_id,
ST_X(coords) as lon,
INNER JOIN geo_countries as gc ON gc.id = id
...
我可以那样做吗?
答
您可以加入一个函数调用表的结果,但在一个稍微不同的方式:
SELECT
gc.col_1, gc.col_2, /* ... as many as needed */
city.id AS city_id, ST_X(city.coords) AS lon
FROM
set_city(1234, 5678, 1.23, 3.45) AS city
JOIN geo_countries AS gc ON gc.id = city.id
因此,在实践中,set_city(...)
的行为在SELECT
仅仅是如果它是任何其他种类的表。
看起来我必须创建第二个函数。这很伤心... – Vitaly
为什么你需要创建第二个功能?做什么的? – joanolo
这对我来说是需要的,因为我不想直接使用查询。为此,我创建了这个问题,因为我不明白如何使用一个函数来完成此任务。现在我有第二个功能,但我认为它不是很好......无论如何感谢您的意见 – Vitaly