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

Question

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

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

现在我像这样调用这个函数:

select * from getStuff('hello');

如果我需要使用 order by 来排序和限制结果，我有什么选择？和 limit条款？

我想这样的查询:

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

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

但即使我是对的，也没有简单的方法可以通过 sql 语言函数的参数传递顺序。只能传递值，不能传递 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 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;

<罢工>这抛出:

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

我读坏了民主党的代码。我已将其更正为问题。这段代码对我有用。

Answer 1

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

• Difference between language sql and language plpgsql in PostgreSQL functions

这比查询中的许多 CASE 子句简单得多:

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

注意事项

使用RETURN QUERY EXECUTE一次性返回查询结果。

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

• Table name as a PostgreSQL function parameter

使用 USING 传递参数值子句以避免再次转换、引用和 SQLi。

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

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