PL/PGSQL всегда возвращает массив или список массивов.

Учитывая простую функцию pl/pgsql:

CREATE OR REPLACE FUNCTION foo (point geometry
                              , OUT _street text
                              , OUT _gid int
                              , OUT distance real)
AS $$
BEGIN 

    SELECT min(distance(point,geom)) as dist, gid, name into distance, _gid, _street
    from streets 
    where geometria && Expand(point,0.001) group by gid, name order by dist limit 1;

END;
$$ LANGUAGE plpgsql;

получается что-то вроде:

 geobase=# select foo(GeomFromText('POINT(-99.124191496999 19.3490666368031)',4326));
                      foo                      
 -------------------------------------------------
 ("PASEO DE LOS FRAMBOYANES",345483,0.000118338)

Что хорошо, за исключением того факта, что я ожидал чего-то более похожего на это:

          _street         |  _gid  |  distance     
--------------------------+--------+-------------
 PASEO DE LOS FRAMBOYANES | 345483 | 0.000118338

Я пробовал варианты с предложением RETURN, определяя его как тип строки, запись и даже таблицу, но всегда получаю кортеж или массив, как показано в примере. Любые подсказки относительно того, как получить результат, похожий на таблицу?


postgresql sql plpgsql nearest-neighbor
person Aaron Rivacoba    schedule 06.08.2015    source источник

Ответы (2)


arrow_upward
0
arrow_downward

На заданный вопрос уже много раз отвечали. Чтобы разложить возвращаемый тип строки, используйте

SELECT * FROM foo( ... );

Как здесь:

Но это еще не все.

Ваша функция с менее запутанным синтаксисом, менее подвержена ошибкам и упрощена:

CREATE OR REPLACE FUNCTION foo (_point geometry)
  RETURNS TABLE (street text, gid int, distance real) AS
$func$
BEGIN

RETRUN QUERY 
SELECT s.name, s.gid, distance(_point, s.geom)
FROM   streets s
WHERE  s.geometria && expand(_point, 0.001)
ORDER  BY 3
LIMIT  1;

END
$func$ LANGUAGE plpgsql;

Поскольку у вас есть ORDER BY ... LIMIT 1, вам вообще не нужны min() и GROUP BY.

Обратите внимание, как я квалифицировал все столбцы, чтобы избежать конфликтов имен.

Есть одно тонкое отличие: если запрос не находит ни одной строки, ваш оригинал вместо этого возвращает строку со значениями NULL, в то время как моя функция фактически не возвращает строку. Как правило, вам нужно последнее.

То же, что эквивалентная простая функция SQL:

CREATE OR REPLACE FUNCTION foo (_point geometry)
  RETURNS TABLE (street text, gid int, distance real) AS
$func$
SELECT s.name, s.gid, distance(_point, s.geom)
FROM   streets s
WHERE  s.geometria && expand(_point, 0.001)
ORDER  BY 3
LIMIT  1
$func$ LANGUAGE sql;

Но это не очень эффективно с большими таблицами. Вам будет интересен поиск KNN (k-ближайший сосед) в PostGis:

person Erwin Brandstetter    schedule 09.08.2015
comment
Большое спасибо за ваш ответ. У меня почти иррациональное отвращение к использованию числовых параметров, как вы делаете в своем примере: SELECT s.name ... ORDER BY 3 Есть ли какое-то преимущество в этом, вместо более словоохотливого ORDER BY расстояний? - person Aaron Rivacoba; 10.08.2015
comment
@AaronRivacoba: нет преимущества в производительности. Просто синтаксический ярлык, который особенно удобен для длинных выражений в списке SELECT. Тем более в примере, где я не назначаю псевдоним столбца, который в противном случае не используется (не виден вне функции). Расстояние псевдонима столбца поднимает вопрос о возможных конфликтах имен с параметром OUT функции (в этом конфликте нет). Вам придется использовать distance(_point, s.geom) AS distance ... ORDER BY distance. Использование автоматического псевдонима кажется еще менее чистым. - person Erwin Brandstetter; 14.08.2015

arrow_upward
1
arrow_downward

попробуй select * from foo(GeomFromText('POINT(-99.124191496999 19.3490666368031)',4326)); ?..

person Vao Tsun    schedule 06.08.2015
comment
Это работает, как задумано! Большое спасибо за ваш быстрый ответ. - person Aaron Rivacoba; 06.08.2015
comment
@AaronRivacoba Если это правильный ответ, вы должны его принять. - person Bill; 07.08.2015