Как ограничить набор результатов с помощью нескольких RETURN QUERY в postgresql

У меня есть функция с несколькими операторами RETURN QUERY. В конце я хочу получить только N случайных записей. Если я установлю LIMIT max_size для каждого оператора RETURN QUERY в конце, я получу max_size * количество RETURN QUERY раз.

Краткая версия моей функции:

CREATE OR REPLACE FUNCTION android_getproposedsongs_test(
                                          IN puserid character varying, max_size int)
    RETURNS TABLE(sid uuid, method text) AS
$BODY$
DECLARE
    songCount int;
BEGIN

RETURN QUERY 
    SELECT trackid as sid, lower('popular') as method
    FROM ratingrecord
    WHERE trackid NOT IN (
       SELECT trackid FROM ratingrecord WHERE userid = puserid)
    AND ratingrecord.rating > 0
    GROUP BY trackid
    HAVING SUM(rating) > 0
    ORDER BY SUM(rating) DESC
    LIMIT max_size;

CREATE TEMP TABLE recommended ON COMMIT DROP 
AS
SELECT trackid, lower('recommended')
FROM ratingrecord finalRate
    INNER JOIN 
    (
        SELECT otherRate.userid AS otherUserId
             , SUM(myRate.rating * otherRate.rating) as SumRating 
        FROM ratingrecord AS myRate
        INNER JOIN ratingrecord AS otherRate 
        ON myRate.trackid = otherRate.trackid
        WHERE myRate.userid = puserid AND myRate.userid != otherRate.userid         
        GROUP BY otherRate.userid
        HAVING SUM(myRate.rating * otherRate.rating) > 0
    )  AS userRelations 
    ON finalRate.userid = userRelations.otherUserId
WHERE finalRate.trackid NOT IN (SELECT trackid FROM ratingrecord WHERE userid = puserid)
GROUP BY finalRate.trackid
HAVING SUM(finalRate.rating * userRelations.SumRating) > 0
ORDER BY SUM(finalRate.rating * userRelations.SumRating) DESC
LIMIT max_size;

RETURN QUERY SELECT * FROM recommended;

-- another RETURN QUERY statements

END;    
$BODY$ 
LANGUAGE plpgsql VOLATILE;

Я хочу что-то вроде ORDER BY random() LIMIT max_size в наборе результатов, но не знаю, куда его поместить.


postgresql sql sql-limit set-returning-functions
person azhidkov    schedule 30.03.2015    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Либо вы используете UNION ALL для объединения всех подзапросов в чистом SQL и добавляете ORDER BY random() LIMIT n один раз в конце.

Или вам нужно самостоятельно отслеживать количество строк, возвращенных в plpgsql, например:

CREATE OR REPLACE FUNCTION android_getproposedsongs_test(puserid varchar, max_size int)
  RETURNS TABLE(sid uuid, method text) AS
$func$
DECLARE
   song_count int;  -- I'd rather not use spurious capitalisation
   running_ct int := 0;
   ct         int;
BEGIN

RETURN QUERY SELECT ... LIMIT max_size; -- apply to each individual query!

GET DIAGNOSTICS ct = ROW_COUNT;
running_ct := running_ct + ct;
IF running_ct >= max_size THEN
   RETURN;
END IF;

RETURN QUERY SELECT ... LIMIT max_size;

GET DIAGNOSTICS ct = ROW_COUNT;
running_ct := running_ct + ct;
IF running_ct >= max_size THEN
   RETURN;
END IF;

-- another RETURN QUERY statements

END 
$func$ LANGUAGE plpgsql VOLATILE;

Связанный:

Однако это возвращает только первые N строки, а не случайный выбор. Чтобы добиться этого, верните все подходящие строки без ограничений и примените ORDER BY random() LIMIT n на следующем шаге:

SELECT *
FROM   android_getproposedsongs_test('foo', 3)
ORDER  BY random() LIMIT 123;

Вы должны знать, что получение случайных строк может быть гораздо дороже, потому что все (возможно, многие) кандидаты должны быть идентифицированы в первую очередь. Возвращать первые N строк может быть намного дешевле.

person Erwin Brandstetter    schedule 30.03.2015