代码之家 › 专栏 › 技术社区 › Sachin

plpgsql函数中的动态ORDER BY和ASC/DESC

dynamic-sql plpgsql sql-order-by postgresql

Sachin · 技术社区 · 8 年前

遵循中提到的方法 this link ,我想通过 ORDER BY 并对函数进行动态排序。

订购人 工作正常,但我无法通过分拣订单( ASC / DESC )。

我现在拥有的:

CREATE OR REPLACE FUNCTION list(_limit integer,_offset integer,sort_by varchar(100), _order varchar(100),_category varchar(100))
  RETURNS TABLE(
     id INTEGER,
     name VARCHAR,
     clientname VARCHAR,
     totalcount BIGINT
  ) AS
$$
DECLARE
   empty text := '';
BEGIN
RETURN Query EXECUTE
'SELECT d.id,
d.name,
d.clientname,
 count(*) OVER() AS full_count FROM design_list as d 
    where ($5 = $6 Or d.category Ilike $5) 
        ORDER BY ' || quote_ident(sort_by) || ' LIMIT $1 offset $2'
USING _limit,_offset,sort_by, _order,_category, empty;
END;
$$  LANGUAGE plpgsql;

2 回复 | 直到 5 年前

Erwin Brandstetter 5 年前

我会这样做:

CREATE OR REPLACE FUNCTION list(
      _category varchar(100)
    , _limit int
    , _offset int
    , _order_by varchar(100)
    , _order_asc_desc text = 'ASC')  -- last param with default value
  RETURNS TABLE(id int, name varchar, clientname varchar, totalcount bigint)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _empty text := '';
BEGIN
   -- Assert valid _order_asc_desc
   IF upper(_order_asc_desc) IN ('ASC', 'DESC', 'ASCENDING', 'DESCENDING') THEN
      -- proceed
   ELSE
      RAISE EXCEPTION 'Unexpected value for parameter _order_asc_desc.
                       Allowed: ASC, DESC, ASCENDING, DESCENDING. Default: ASC';
   END IF;
   
   RETURN QUERY EXECUTE format(
     'SELECT id, name, clientname, count(*) OVER() AS full_count
      FROM   design_list
      WHERE ($1 = $2 OR category ILIKE $1) 
      ORDER  BY %I %s
      LIMIT  %s
      OFFSET %s'
    , _order_by, _order_asc_desc, _limit, _offset)
   USING _category, _empty;
END
$func$;

核心功能:使用 format() 安全而优雅地连接查询字符串。相关:

ASC / DESC (或 ASCENDING / DESCENDING )是固定的关键字。我添加了手动检查( IF ... )然后用一个简单的 %s .那是一主张法律意见的方式。为了方便起见,我为意外输入添加了错误消息和参数默认值,因此函数默认为 ASC公司 如果调用中省略了最后一个参数。相关:

寻址 Pavel's valid comment ,I连接 _limit 和 _offset 直接查询,因此已使用这些参数计划查询。

_限制 和 _偏移量 是 integer 参数,因此我们可以使用 %s 没有SQL注入的危险。您可能希望在连接之前断言合理的值(排除负值和过高的值)。。。

其他注意事项:

使用一致的命名约定。我用下划线作为所有参数和变量的前缀 _ ,而不仅仅是一些。
内部未使用表格限定 EXECUTE ,因为只涉及一个表 处决 有其单独的范围。
为了澄清,我重命名了一些参数。 _order_by 而不是 _sort_by ; _order_asc_desc 而不是 _order 。

Mike Packer 6 年前

非动态sql解决方案。

CREATE OR REPLACE FUNCTION list(
...
in_use_asc boolean default false,
_order_by varchar(100)
..
)
..


CREATE TEMPORARY TABLE tempHolder ON COMMIT DROP AS
SELECT SELECT id, name, clientname, count(*) OVER() AS full_count
      FROM   design_list
      WHERE ($1 = $2 OR category ILIKE $1);

     IF in_use_asc = TRUE THEN
        RETURN QUERY SELECT * FROM tempHolder ORDER BY _order_by asc LIMIT {} OFFSET {};
    ELSE 
        RETURN QUERY SELECT * FROM tempHolder ORDER BY _order_by desc LIMIT {} OFFSET {};
    END IF;

不应该再慢了,因为SQL无论如何都需要获取所有内容,因为ORDER BY加上您避免了动态SQL。