我有一个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
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.