PostgreSQL 参数化 Order By/Limit in table 函数 [英] PostgreSQL parameterized Order By / Limit in table function

问题描述

我有一个执行简单的 sql select 语句的 sql 函数:

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');

如果我需要使用 order by 和 limit 子句对结果进行排序和限制，我有哪些选择?

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

我猜是这样的查询:

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

效率不会很高，因为表 stuff 中的所有行都将由函数 getStuff 返回，然后才按限制排序和切片.

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.

但即使我是对的，也没有简单的方法可以通过 sql 语言函数的参数传递顺序.只能传递值，不能传递sql语句的一部分.

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.

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

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.

那么，有没有其他方法可以实现这一目标?或者你会选择什么选项?函数外的排序/限制，还是plpgsql?

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

我使用的是 postgresql 9.1.

I am using postgresql 9.1.

我像这样修改了 CREATE FUNCTION 语句:

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;

<打击>这抛出:

错误:无法匹配 CASE 类型的字符和整数ŘÁDKA 13:当 $1 = 'parent' THEN t.parent

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

stuff 表如下所示:

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),
    )

编辑2

我读错了 Dems 代码.我已将其纠正为问题.此代码对我有用.

Edit2

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

推荐答案

plpgsql 函数对于任何稍微复杂的事情都没有错.性能会受到影响的唯一情况是嵌套 plpgsql 函数时，因为查询计划器无法进一步优化外部查询上下文中包含的代码，这可能会也可能不会使其变慢.
稍后的答案中有更多详细信息:

There is nothing wrong with a plpgsql function for anything a little more complex. The only situation where performance can suffer is when a plpgsql function is nested, because the query planner cannot further optimize the contained code in the context of the outer query which may or may not make it slower.
More details in this later answer:

• 语言 sql 和PostgreSQL 函数中的语言 plpgsql

手头的案例比查询中的许多CASE子句简单得多:

In the case at hand 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
$func$
BEGIN
   RETURN QUERY EXECUTE '
      SELECT *
      FROM   stuff
      WHERE  col = $1
      ORDER  BY ' || quote_ident(_orderby) || ' ASC
      LIMIT  $2'
   USING _param, _limit;
END
$func$  LANGUAGE plpgsql;

调用:

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

注意事项

使用返回查询执行 一次性返回查询结果.

Notes

Use RETURN QUERY EXECUTE to return the results of query in one go.

使用quote_ident()用于防止 SQLi 的标识符.
或者 format() 对于任何更复杂的事情.见:

Use quote_ident() for identifiers to safeguard against SQLi.
Or format() for anything more complex. See:

• 作为 PostgreSQL 函数参数的表名

使用 USING 子句以避免再次强制转换、引用和 SQLi.

Pass parameter values with the USING clause to avoid casting, quoting and SQLi once again.

注意不要在参数和列名之间产生命名冲突.在示例中，我用下划线 (_) 作为参数名称的前缀.只是我个人的喜好.

Be careful not to create naming conflicts between parameters and column names. I prefixed parameter names with an underscore (_) in the example. Just my personal preference.

编辑后的第二个函数无法工作，因为您只返回 parent 而返回类型声明为 SETOF stuff.您可以声明任何您喜欢的返回类型，但实际返回值必须与声明匹配.您可能想要使用 RETURNS TABLE为此.

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 actual return values have to match the declaration. You might want to use RETURNS TABLE for that.

这篇关于PostgreSQL 参数化 Order By/Limit in table 函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！