Функция PostgreSQL, повторяющая запрос, как вернуть таблицу

У меня есть таблица с идентификатором, временем и столбцом объекта PostGIS. Таблица содержит несколько точек для каждого идентификатора в разное время. Я хочу найти среднее расстояние набора точек одного идентификатора до набора точек другого идентификатора для каждой точки, которая происходит в одно и то же время, и найти это среднее значение для всех других наборов точек идентификатора по сравнению с исходным набором идентификаторов.

Пока у меня есть эта функция:

CREATE TYPE score AS (id int, dist float);
CREATE OR REPLACE FUNCTION avgdist(id1 int) RETURNS TABLE (id int, dist float) LANGUAGE plpgsql AS 
$func$ 
    DECLARE 
    scores score; 
    id2 int; 
    set2 record; 
    begin 
        id2:= 0; 
        IF (id1 = id2 ) THEN 
            id2:= 1; 
        END IF; 
        FOR set2 IN 
        SELECT my_table.id, my_table.time, my_table.geom FROM my_table WHERE my_table.id = id2 loop 
            id2:= id2 + 1; 
            CONTINUE WHEN id1 = id2; 
            EXECUTE 'WITH origin AS (SELECT time, id, geom FROM my_table WHERE id = $1)
                SELECT id, avg(ST_Distance(origin.geom, $2)) 
                FROM origin WHERE origin.time = $3 
                group by origin.id 
                ORDER BY id' 
                    INTO scores 
                    USING id1, set2.geom, set2.time; 
        end loop; 
        RETURN; 
    end 
$func$;

При вызове этой функции с помощью select * from avgdist(2) я не получаю никаких результатов, то же самое с slect avgdist(2). Я получаю результаты, когда запускаю запрос в рамках выполнения самостоятельно в psql с подключенными значениями.

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

Помощь в этом будет оценена, или есть лучший способ достичь желаемых результатов без зацикливания?


postgresql plpgsql sql-function
person mcody    schedule 02.05.2019    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Есть еще вопросы:

  • Пункт RETURNS TABLE определяет OUT переменных. Вы можете использовать эти переменные.

    CREATE OR REPLACE FUNCTION foo()
RETURNS TABLE (r1 int, r2 int)
-- you don't need aux variables for result
...
  r1 := 10; r2 := 10;
  RETURN NEXT;

  • EXECUTE INTO может хранить только первую строку (или значение) из результата динамического запроса.

  • RETURN останавливает оценку функции. Вы должны использовать RETURN NEXT или RETURN QUERY.

    FOR x, y IN SELECT ..
LOOP
  -- when only first row of result is interesting
  EXECUTE '..' INTO r1, r2 USING x, y;
  RETURN NEXT;
END LOOP

    или с RETURN QUERY EXECUTE

    FOR x, y IN SELECT ..
LOOP
  RETURN QUERY EXECUTE '..' USING x, y;
END LOOP

  • Я не понимаю ваш код, но, похоже, вам не нужно использовать динамические команды SQL - EXECUTE. Динамический SQL необходим только тогда, когда у вас есть переменная вместо идентификаторов SQL. Нет этого дела. И тот же случай - почему вы используете предложение CTE WITH. В этом нет необходимости — и это может иметь негативные последствия (нежелательная материализация — исправлено в PostgreSQL 12). Используйте только RETURN QUERY (без EXECUTE) и передайте запрос как запрос (а не как строку).

Документация по этим командам хорошая — https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

person Pavel Stehule    schedule 02.05.2019
comment
Спасибо, ваш ответ помог мне понять, как использовать RETURNS TABLE вместе с RETURN NEXT. Причина, по которой вы не могли понять мой код, заключается в том, что он совершенно неправильно понял то, что я пытался сделать, что мне даже не понадобился цикл для начала. Простой avg() с GROUP BY id делает то, что мне нужно. - person mcody; 02.05.2019