PostgreSQL在表函数中参数化了“排序依据/限制”

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 byandlimit子句对结果进行排序和限制，我有哪些选择？

我猜这样的查询：

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;

该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代码。我已将其更正为问题。这段代码对我有用。

Answer 1

plpgsql函数对于更复杂的东西没有任何问题。可能会影响性能的唯一情况是嵌套plpgsql函数时，因为查询计划程序无法在外部查询的上下文中进一步优化所包含的代码，这可能会使速度变慢，也可能不会使其变慢。
在此稍后的答案中有更多详细信息：

PostgreSQL函数中的语言sql和语言plpgsql之间的区别
在这种情况下，它比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()更复杂的事情。看：

表名作为PostgreSQL函数参数
将参数值与USING子句一起传递，以避免再次进行强制转换，引号和SQLi。

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

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