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

发布时间：2020-12-13 16:33:49 所属栏目：百科来源：网络整理

导读：我有一个sql函数做一个简单的sql select语句： 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('h

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

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

如果我需要按顺序和限制条件订购和限制结果,我有什么选择？

我猜这样的查询：

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

将不会非常有效,因为来自表格的所有行都将由函数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

东西表如下所示：

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

我读了Dems的代码.我已经纠正了问题.这段代码正在为我工??作.

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

呼叫：

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

笔记

>一次性使用RETURN QUERY EXECUTE返回查询结果.
>使用quote_ident()标识符来防范SQLi.
>使用USING交换参数值,以避免再次引用,引用和SQLi.
>小心不要在参数和列名之间创建命名冲突.我在我的参数名称前面加上“_”.

编辑后您的第二个功能无法正常工作,因为只有在返回类型被声明为SETOF时才返回父级.您可以声明任何您喜欢的返回类型,但实际的返回值必须与该声明相匹配.你可能想要使用RETURNS TABLE.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!