问题描述
遵循此链接中提到的方法 ,我想将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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!