pl/pgsql не возвращает все результаты, используя запись

Я пытаюсь создать сложную функцию pl/pgsql, которая собирает некоторые результаты запроса, а затем проверяет каждый из них и возвращает его или нет.

Это мой код на данный момент. Меня смущают record и loop части.

CREATE  FUNCTION __a_search_creator(creator text, ordertype integer, orderdate integer, areaid bigint) RETURNS record
AS $$
DECLARE    
fromText text;
whereText text;
usingText text; 
firstrecord record;
areageom geometry;

BEGIN
    IF areaid IS NOT NULL
    THEN        
        EXECUTE format('SELECT area.geom FROM area WHERE area.id=$1')
        INTO areageom
        USING areaid;  

        FOR firstrecord IN 
        EXECUTE format( 
        'SELECT place.id, person.name, place.geom 
        FROM '||fromText||'     
        WHERE '||whereText)  
        USING  creator, ordertype , orderdate         
        LOOP    
        --return only data that the place.geom is inside areageom using PostGIS 
        END LOOP;
    END IF;
    RETURN firstrecord;
END;
$$
LANGUAGE plpgsql;

Я планирую сделать некоторые дополнительные проверки в loop, как вы можете видеть, и RETURN только данные о том, что place.geom (долгота/широта) находится внутри areageom. Но так как я новичок в pl/pgsql, я сейчас создаю первый шаг, просто собираю все данные, помещаю их в record и возвращаюсь.

Моя проблема в том, что, что бы я ни пытался, я получаю только один результат. Я вызываю select __a_search_creator('johnson',8, 19911109, 20); и получаю 1,"seth johnson", 65485,84545, но я знаю, что должен получить еще одну строку результатов. Происходит перезапись?

Я пытался поставить RETURN NEXT firstrecord; Я пробовал что-то вроде

        select place.id from place INTO placeid;        
        select person.name from person INTO personname;
        select place.geom from place INTO placegeom;        
        firstrecord.id := placeid;
        firstrecord.name := personname;
        firstrecord.geom := placegeom;

Это по-прежнему возвращает только один результат, я пробовал тестировать только этот RAISE NOTICE '%', firstrecord.id;, который по-прежнему возвращает только один полный набор результатов.

Я не знаю, как поступить, пожалуйста, посоветуйте.


postgresql for-loop resultset plpgsql set-returning-functions
person slevin    schedule 30.01.2018    source источник
comment
Не связано, но: динамический SQL в первом запросе бесполезен. Вы можете заменить это простым SELECT area.geom INTO areageom FROM area WHERE area.id = areaid;   -  person a_horse_with_no_name    schedule 31.01.2018

Ответы (2)


arrow_upward
0
arrow_downward

Вы объявили свою функцию с помощью RETURNS record, поэтому она возвращает одну запись (неизвестного типа).

Используйте RETURNS SETOF record, чтобы вернуть набор строк. Руководство:

Модификатор SETOF указывает, что функция вернет набор элементов, а не один элемент.

А еще лучше используйте RETURNS TABLE с правильным определением таблицы. Как вы уже сказали об этом в своих последних вопросах:

Связанные (с подробным объяснением и ссылками на руководство):

person Erwin Brandstetter    schedule 31.01.2018

arrow_upward
0
arrow_downward

Это мое решение проблемы

IF areaid IS NOT NULL
THEN                        
    EXECUTE format('SELECT area.geom FROM area WHERE area.id=$1')
    INTO areageom
    USING areaid;                      

    FOR firstrecord IN 
    EXECUTE format( 
    'SELECT place.id, person.name, place.geom FROM '||fromText||' WHERE '||whereText)          
    USING  creator, ordertype, orderdate

    LOOP
        IF ST_Within(firstrecord.geom , areageom)
        THEN
            RETURN QUERY VALUES(firstrecord.id, firstrecord.name, firstrecord.geom);
        END IF;
    END LOOP;        
END IF;
RETURN;

Спасибо @Erwin Brandstetter за полезные ссылки.

person slevin    schedule 01.02.2018