PostgreSQL中的动态限制

Question

我有一个返回员工列表的函数。我的要求是，如果我向该函数传递了Limit参数，则应该返回带有限制和偏移量的结果。如果我没有传递limit参数，则应该返回所有行。

例如：

When Limit is greater than 0(I am passing Limit as 10)
Select * from Employees
Limit 10 offset 0

When Limit is equal to 0 than
Select * from Employees

有没有办法在函数中执行这样的逻辑？

- manthan davda

Answer 1

是的，您可以传递一个表达式到 LIMIT 和 OFFSET 子句中，其中包括使用传递到函数中的参数。

CREATE FUNCTION employees_limited(limit integer) RETURNS SET OF employees AS $$
BEGIN
  IF limit = 0 THEN
    RETURN QUERY SELECT * FROM employees;
  ELSE
    RETURN QUERY SELECT * FROM employees LIMIT (limit)
  END IF;
  RETURN;
END; $$ LANGUAGE plpgsql STRICT;

注意在 LIMIT 子句周围的括号。同样，您可以传入一个 OFFSET 值。

然而，这个例子非常琐碎。您可以通过在函数外部进行 LIMIT 来实现相同的效果：

SELECT * FROM my_function() LIMIT 10;

只有在需要处理大量数据的复杂查询时，将此操作放入函数中才真正有用。

此外，请注意，没有ORDER BY的LIMIT子句会产生不可预测的结果。

- Patrick

---

感谢您的回答，您的解决方案在我们有简单查询时运行良好。但是，如果我的查询有150行，那么我无法实现上述解决方案，因为我的函数会变得非常庞大（行数过多），没有必要这样做。 - manthan davda

Answer 2

抱歉，我无法评论。解决方案几乎由Patrick提供。
首先，我们应编写函数以无限制地返回结果。

CREATE FUNCTION test () 
RETURNS TABLE (val1 varchar, val2 integer) AS $$
    BEGIN
    RETURN QUERY SELECT val1, val2 FROM test_table;
    END;
$$ LANGUAGE plpgsql;

然后我们需要编写包装函数，它将处理限制。

CREATE FUNCTION test_wrapper (l integer DEFAULT 0)
RETURNS TABLE (name varchar, id integer) AS $$
    BEGIN
        IF l = 0 THEN
            RETURN QUERY SELECT * FROM test(); -- returns everything
        ELSE
            RETURN QUERY SELECT * FROM test() LIMIT (l); -- returns accordingly
        END IF;
    END;
$$ LANGUAGE plpgsql;

在我的情况下，我需要将表格作为最终结果返回，但是可以从包装函数中获取所需的任何返回值。

- Dmitry

Answer 3

我在postgres中使用了以下方法

Create Function employees_limited(limit integer, offset interger, pagination boolean) RETURNS SET OF employees AS $$
    BEGIN
        if pagination = false then --skip offset and limit
            offset = 0;
            limit = 2147483647; -- int max value in postgres
        end if;
        RETURN QUERY 
        SELECT * FROM employees order by createddate 
        LIMIT (limit) Offset offset; 
      RETURN;
    END; $$ LANGUAGE plpgsql STRICT;

- Seethapriyanka Peddada

Answer 4

请注意，带有LIMIT的SELECT语句应该始终包括ORDER BY，因为如果没有明确指定，则返回行的顺序可能是未定义的。

您可以使用ROW_NUMBER()代替LIMIT，如下面的查询所示：

SELECT *
FROM (
  SELECT *, row_number() OVER (ORDER BY id) AS rn
  FROM Employees
) AS s
WHERE
  (rn>:offset AND rn<=:limit+:offset) OR :limit=0

- fthiella