PostgreSQL参数化表函数中的Order By / Limit

本文介绍了PostgreSQL参数化表函数中的Order By / Limit的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个sql函数，它做一个简单的sql select语句：

CREATE OR REPLACE FUNCTION getStuff 返回SETOF的东西AS $ BODY $ select * from stuff 其中col = $ 1 $ BODY $ LANGUAGE sql;

现在我调用这个函数：

select * from getStuff（'hello'）;

如果我需要订购并限制结果，由和限制条款？

我猜这样的查询：

select * from getStuff（'hello'）order by col2 limit 100;

不会非常有效，因为表 stuff 将由函数 getStuff 返回，然后按顺序排序和限制。

我是对的，没有简单的方法如何通过sql语言函数的参数传递顺序。

另一种选择是在 plpgsql 语言中创建函数，其中可以构造查询并通过 EXECUTE 执行它。但是这不是一个很好的方法。

那么，有没有其他方法实现呢？ 或者你会选择什么选项？订阅/限制函数外部，或plpgsql？

我使用postgresql 9.1。

编辑

我修改了CREATE FUNCTION语句，如下所示：

CREATE OR REPLACE FUNCTION getStuff （param character varying，orderby character varying） RETURNS SETOF stuff AS $ BODY $ select t。* from t 其中col = $ 1 ORDER BY CASE WHEN $ 2 ='parent'THEN t.parent END， CASE WHEN $ 2 ='type'THEN t。typeEND， CASE WHEN $ 2 ='title' THEN t.title END $ BODY $ LANGUAGE sql;

此投掷：

$ b b

错误：CASE类型字符变化和整数不能匹配ŘÁDKA13：WHEN $ 1 ='parent'THEN t.parent

stuff 表如下所示：

CREATE TABLE stuff （ id integer，typeinteger NOT NULL，父整数，标题字符变化（100）NOT NULL，描述文本， CONSTRAINTpkIdPRIMARY KEY（id），） pre> Edit2

我没有读过Dems代码。我已经纠正它的问题。

解决方案

plpgsql函数没有问题。它是最优雅和最快的解决方案，任何东西更复杂一点。唯一遇到性能可能会受到影响的情况是嵌套plpgsql函数，因为查询计划程序无法进一步优化外部查询的上下文中的代码，这可能会或不会使其变慢。 更多细节在此后的答案：

$

b $ b

在这种情况下，它比查询中的大量 CASE 子句简单得多：

CREATE OR REPLACE FUNCTION get_stuff（_param text，_orderby text，_limit int） RETURNS SETOF stuff as $ BODY $ BEGIN RETURN QUERY EXECUTE' SELECT * FROM stuff WHERE col = $ 1 ORDER BY'|| quote_ident（_orderby）|| ' LIMIT $ 2'使用_param，_limit; END; $ BODY $ LANGUAGE plpgsql;

呼叫：

code> SELECT * FROM get_stuff（'hello'，'col2'，100）;

注意

• 使用返回查询执行返回结果
• 使用 quote_ident（）用于防止SQLi的标识符。
• 使用 使用 传递参数值，以避免转换，引用和SQLi。
• 注意不要在参数和列名称之间创建命名冲突。

编辑后的第二个函数不能工作，因为你只返回 parent ，而返回类型声明为 SETOF stuff 。您可以声明所需的任何返回类型，但实际的返回值必须与该声明相匹配。您可能需要使用返回表。

I have a sql function that does a simple sql select statement:

CREATE OR REPLACE FUNCTION getStuff(param character varying) RETURNS SETOF stuff AS $BODY$ select * from stuff where col = $1 $BODY$ LANGUAGE sql;

For now I am invoking this function like this:

select * from getStuff('hello');

What are my options if I need to order and limit the results with order by and limit clauses?

I guess a query like this:

select * from getStuff('hello') order by col2 limit 100;

would not be very efficient, because all rows from table stuff will be returned by function getStuff and only then ordered and sliced by limit.

But even if I am right, there is no easy way how to pass the order by argument of an sql language function. Only values can be passed, not parts of sql statement.

Another option is to create the function in plpgsql language, where it is possible to construct the query and execute it via EXECUTE. But this is not a very nice approach either.

So, is there any other method of achieving this? Or what option would you choose? Ordering/limiting outside the function, or plpgsql?

I am using postgresql 9.1.

Edit

I modified the CREATE FUNCTION statement like this:

CREATE OR REPLACE FUNCTION getStuff(param character varying, orderby character varying) RETURNS SETOF stuff AS $BODY$ select t.* from stuff t where col = $1 ORDER BY CASE WHEN $2 = 'parent' THEN t.parent END, CASE WHEN $2 = 'type' THEN t."type" END, CASE WHEN $2 = 'title' THEN t.title END $BODY$ LANGUAGE sql;

This throws:

ERROR: CASE types character varying and integer cannot be matched ŘÁDKA 13: WHEN $1 = 'parent' THEN t.parent

The stuff table looks like this:

CREATE TABLE stuff ( id integer serial, "type" integer NOT NULL, parent integer, title character varying(100) NOT NULL, description text, CONSTRAINT "pkId" PRIMARY KEY (id), )

Edit2

I have badly read Dems code. I have corrected it to question. This code is working for me.

解决方案

There is nothing wrong with a plpgsql function. It is the most elegant and fastest solution for anything a little more complex. The only situation where performance could suffer is when you nest plpgsql functions because the query planner cannot further optimize code inside in the context of the outer query which may or may not make it slower. More details in this later answer:

• Difference between language sql and language plpgsql in PostgreSQL functions

In this case it is much simpler than lots of CASE clauses in a query:

CREATE OR REPLACE FUNCTION get_stuff(_param text, _orderby text, _limit int) RETURNS SETOF stuff AS $BODY$ BEGIN RETURN QUERY EXECUTE ' SELECT * FROM stuff WHERE col = $1 ORDER BY ' || quote_ident(_orderby) || ' LIMIT $2' USING _param, _limit; END; $BODY$ LANGUAGE plpgsql;

Call:

SELECT * FROM get_stuff('hello', 'col2', 100);

Notes

• Use RETURN QUERY EXECUTE to return the results of query in one go.
• Use quote_ident() for identifiers to safeguard against SQLi.
• Use USING to hand in parameter values to avoid casting, quoting and SQLi once more.
• Be careful not to create naming conflicts between parameters and columns names. I prefixed my parameter names with "_" in the example.

Your second function after the edit cannot work, because you only return parent while the return type is declared SETOF stuff. You can declare any return type you like, but the actual return values have to match that declaration. You might want to use RETURNS TABLE for that.