PostgreSQL 参数化 Order By / Limit in table 函数

Question

提问by JoshuaBoshi

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

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

CREATE OR REPLACE FUNCTION getStuff(param character varying)
  RETURNS SETOF stuff AS
$BODY$
    select *
    from stuff
    where col = 
$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 byand limitclauses?

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

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 stuffwill be returned by function getStuffand only then ordered and sliced by limit.

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

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.

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

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

另一种选择是用plpgsql语言创建函数，在这种情况下可以构造查询并通过EXECUTE. 但这也不是一个很好的方法。

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

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

I am using postgresql 9.1.

我正在使用 postgresql 9.1。

Edit

编辑

I modified the CREATE FUNCTION statement like this:

我像这样修改了 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 = 
    ORDER BY
        CASE WHEN  = 'parent' THEN t.parent END,
        CASE WHEN  = 'type' THEN t."type" END, 
        CASE WHEN  = '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~~

~~错误：CASE 类型字符变化和整数无法匹配？áDKA 13：WHEN $1 = 'parent' THEN t.parent~~

The stufftable looks like this:

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

Edit2

编辑2

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

我严重阅读了 Dems 代码。我已将其纠正为问题。这段代码对我有用。

Answer 1

回答by Erwin Brandstetter

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 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:

plpgsql 函数没有任何问题。对于稍微复杂一点的事情，它是最优雅、最快速的解决方案。性能会受到影响的唯一情况是嵌套 plpgsql 函数时，因为查询计划器无法进一步优化外部查询上下文中包含的代码，这可能会也可能不会使其变慢。
稍后的答案中的更多详细信息：

Difference between language sql and language plpgsql in PostgreSQL functions

PostgreSQL 函数中语言 sql 和语言 plpgsql 的区别

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

在这种情况下，它比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 = 
      ORDER  BY ' || quote_ident(_orderby) || ' ASC
      LIMIT  '
   USING _param, _limit;
END
$func$  LANGUAGE plpgsql;

Call:

称呼：

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

Notes

笔记

Use RETURN QUERY EXECUTEto return the results of query in one go.
Use quote_ident()for identifiers to safeguard against SQLi. Or format()for anything more complex. Related:
- Table name as a PostgreSQL function parameter
Hand in parameter values with the USINGclause 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.

用于RETURN QUERY EXECUTE一次性返回查询结果。
使用quote_ident()标识符反对SQLI保障。或者format()任何更复杂的东西。有关的：
- 表名作为 PostgreSQL 函数参数
使用USING子句传递参数值以避免再次强制转换、引用和 SQLi。
注意不要在参数和列名之间产生命名冲突。_在示例中，我使用下划线 ( )作为参数名称的前缀。只是我的个人喜好。

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

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

Answer 2

回答by dmg

If your function is stable(does not modify the database), the query planner will typically inlineit. Therefore, doing SELECT * FROM getStuff('x') LIMIT 10will produce the same query plan as if the limit were inside getStuff().

如果您的函数是稳定的（不修改数据库），查询规划器通常会内联它。因此，这样做SELECT * FROM getStuff('x') LIMIT 10将产生与限制在 inside 相同的查询计划getStuff()。

However, you need to tell PG your function is stable by declaring it as such:

但是，您需要通过如下声明来告诉 PG 您的函数是稳定的：

CREATE OR REPLACE FUNCTION getStuff(param varchar)
RETURNS setof STUFF
LANGUAGE SQL
STABLE
AS $$ ... $$;

Now doing EXPLAIN SELECT * FROM getStuff('x') LIMIT 1should produce the same query plan as writing out the equivalent query would.

现在做EXPLAIN SELECT * FROM getStuff('x') LIMIT 1应该产生与写出等效查询相同的查询计划。

The inlining should also work for ORDER BYclauses outside the function. But if you wanted to parameterize the function to determine the order by, you could do it like this to also control the sort direction:

内联也应该适用于ORDER BY函数之外的子句。但是如果你想参数化函数来确定顺序，你可以这样做来控制排序方向：

CREATE FUNCTION sort_stuff(sort_col TEXT, sort_dir TEXT DEFAULT 'asc')
RETURNS SETOF stuff
LANGUAGE SQL
STABLE
AS $$
    SELECT *
    FROM stuff
    ORDER BY
      -- Simplified to NULL if not sorting in ascending order.
      CASE WHEN sort_dir = 'asc' THEN
          CASE sort_col
              -- Check for each possible value of sort_col.
              WHEN 'col1' THEN col1
              WHEN 'col2' THEN col2
              WHEN 'col3' THEN col3
              --- etc.
              ELSE NULL
          END
      ELSE
          NULL
      END
      ASC,

      -- Same as before, but for sort_dir = 'desc'
      CASE WHEN sort_dir = 'desc' THEN
          CASE sort_col
              WHEN 'col1' THEN col1
              WHEN 'col2' THEN col2
              WHEN 'col3' THEN col3
              ELSE NULL
          END
      ELSE
          NULL
      END
      DESC
$$;

As long as sort_coland sort_dirare constant within the query, the query planner should be able to simplify the verbose looking query to

只要sort_col和sort_dir在查询中保持不变，查询计划器就应该能够将冗长的查询简化为

SELECT *
FROM stuff
ORDER BY <sort_col> <sort_dir>

which you can verify using EXPLAIN.

您可以使用EXPLAIN.

Answer 3

回答by Tom H

As to the ORDER BYyou could try something like this:

至于ORDER BY你可以尝试这样的事情：

SELECT
    <column list>
FROM
    Stuff
WHERE
    col1 = 
ORDER BY
    CASE 
        WHEN 'col1' THEN col1
        WHEN 'col2' THEN col2
        WHEN 'col3' THEN col3
        ELSE col1  -- Or whatever your default should be
    END

You might have to do some data type conversions so that all of the data types in the CASEresult match. Just be careful about converting numerics to strings - you'll have to prepend 0s to make them order correctly. The same goes for date/time values. Order by a format that has year followed by month followed by day, etc.

您可能需要进行一些数据类型转换，以便结果中的所有数据类型都CASE匹配。在将数字转换为字符串时要小心——你必须在前面加上 0 才能使它们正确排序。日期/时间值也是如此。按年、月、日等格式排序。

I've done this in SQL Server, but never in PostgreSQL, and I don't have a copy of PostgreSQL on this machine, so this is untested.

我在 SQL Server 中做过这个，但从来没有在 PostgreSQL 中做过，而且我在这台机器上没有 PostgreSQL 的副本，所以这是未经测试的。

Answer 4

回答by soulcheck

You can pass limit value as a function argument without any problems. As for ordering you can use ODER BY in combination with CASE statement. This unfortunately won't work for something like

您可以毫无问题地将限制值作为函数参数传递。至于排序，您可以将 ODER BY 与 CASE 语句结合使用。不幸的是，这不适用于类似的事情

ORDER BY CASE condition_variable
WHEN 'asc' THEN column_name ASC
ELSE column_name DESC
END;

PostgreSQL 参数化 Order By / Limit in table 函数

提问by JoshuaBoshi

Edit

编辑

Edit2

编辑2

回答by Erwin Brandstetter

Notes

笔记

回答by dmg

回答by Tom H

回答by soulcheck

相关推荐

最近更新

标签

PostgreSQL 参数化 Order By / Limit in table 函数

提问by JoshuaBoshi

Edit

编辑

Edit2

编辑2

回答by Erwin Brandstetter

Notes

笔记

回答by dmg

回答by Tom H

回答by soulcheck

相关推荐

postgresql 使用 Hibernate 查询：冒号被视为参数/转义冒号

postgresql Postgis / Geodjango：无法确定数据库的 PostGIS 版本

postgresql 在 postgres 中使用 COPY FROM - 本地文件的绝对文件名

PostgreSQL 搜索中的特殊字符

相关推荐

最近更新

标签