plpgsql函数中的动态ORDER BY和ASC/DESC [英] Dynamic ORDER BY and ASC / DESC in a plpgsql function
问题描述
遵循此链接中提到的方法 ，我想将ORDER BY和排序顺序动态传递给函数.
Following the approach mentioned in this link, I want to pass ORDER BY and sorting order to a function dynamically.
ORDER BY工作正常，但我无法通过排序顺序(ASC/DESC).
ORDER BY is working fine but I am not able to pass sorting order (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;
推荐答案
我会这样:
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) 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$ LANGUAGE plpgsql;
核心功能:使用 format() 来安全优雅地连接您的查询字符串.相关:
Core feature: use format() to safely and elegantly concatenate your query string. Related:
- INSERT with dynamic table name in trigger function
- Format specifier for integer variables in format() for EXECUTE?
ASC/DESC(或ASCENDING/DESCENDING)是固定关键字.我添加了手动检查(IF ...)，后来又连接了一个简单的%s.这是断言合法输入的一种一种方式.为了方便起见，我添加了一条错误消息，用于意外输入和参数默认值，因此，如果调用中省略了最后一个参数，则该函数默认为ASC.相关:
ASC / DESC (or ASCENDING / DESCENDING) are fixed key words. I added a manual check (IF ...) and later concatenate with a simple %s. That's one way to assert legal input. For convenience, I added an error message for unexpected input and a parameter default, so the function defaults to ASC if the last parameter is omitted in the call. Related:
- Optional argument in PL/pgSQL stored procedure
- ERROR: input parameters after one with a default value must also have defaults
处理 Pavel的有效评论，我直接将_limit和_offset连接起来，因此已经使用这些参数计划了查询.
Addressing Pavel's valid comment, I concatenate _limit and _offset directly, so the query is already planned with those parameters.
_limit和_offset是integer参数，因此我们可以使用普通的%s而不会有SQL注入的危险.您可能需要在连接...之前声明合理的值(排除负值和过高的值).
_limit and _offset are integer parameters, so we can use plain %s without the danger of SQL injection. You might want to assert reasonable values (exclude negative values and values too high) before concatenating ...
其他说明:
-
使用一致的命名约定.我为所有参数和变量加了下划线
_，而不仅仅是 some .
在EXECUTE内部不使用表限定，因为仅涉及一个表，并且EXECUTE具有其单独的范围.
Not using table qualification inside EXECUTE, since there is only a single table involved and the EXECUTE has its separate scope.
我重命名了一些参数以进行说明. _order_by代替_sort_by; _order_asc_desc而不是_order.
I renamed some parameters to clarify. _order_by instead of _sort_by; _order_asc_desc instead of _order.
这篇关于plpgsql函数中的动态ORDER BY和ASC/DESC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
