我试图使用PL / pgsql编写一个带有可选参数的存储过程。目的是编写一个基于过滤的记录集(如果指定)执行查询的函数,否则对表中的整个数据集执行查询。
例如(PSEUDO CODE):
CREATE OR REPLACE FUNCTION foofunc(param1 integer,param2 date,optional_list_of_ids=[]) RETURNS SETOF RECORD AS $$ IF len(optional_list_of_ids) > 0 THEN RETURN QUERY (SELECT * from foobar where f1=param1 AND f2=param2 AND id in optional_list_of_ids); ELSE RETURN QUERY (SELECT * from foobar where f1=param1 AND f2=param2); ENDIF $$ LANGUAGE sql;
除此之外,我想知道我如何在另一个外部函数中调用这样一个函数。这是怎么做的 – 是正确的还是有更好的方法?
CREATE FUNCTION foofuncwrapper(param1 integer,param2 date) RETURNS SETOF RECORD AS $$ BEGIN CREATE TABLE ids AS SELECT id from foobar where id < 100; RETURN QUERY (SELECT * FROM foofunc(param1,param2,ids)); END $$ LANGUAGE sql
因为Postgresql 8.4(你似乎正在运行),有
default values for function parameters.如果你最后把参数提供一个默认值,你可以直接从调用中省略它:
CREATE OR REPLACE FUNCTION foofunc(_param1 integer,_param2 date,_ids int[] DEFAULT '{}') RETURNS SETOF foobar AS -- declare return type! $BODY$ BEGIN -- required for plpgsql IF _ids <> '{}'::int[] THEN -- excludes empty array and NULL RETURN QUERY SELECT * FROM foobar WHERE f1 = _param1 AND f2 = _param2 AND id = ANY(_ids); -- "IN" is not proper Syntax for arrays ELSE RETURN QUERY SELECT * FROM foobar WHERE f1 = _param1 AND f2 = _param2; END IF; END; -- required for plpgsql $BODY$ LANGUAGE plpgsql;
我从你的凌乱的例子中删除了冗余的param1。
>由于你返回SELECT * FROM foobar,使用RETURNS SETOF foobar而不是RETURNS SETOF记录。后一种形式非常笨重,因为您必须在每次通话中提供列定义列表。
>我使用整数int []的数组作为函数参数。相应地改编了IF表达式和WHERE子句。
> IF语句在纯sql中不可用。必须要LANGUAGE plpgsql为此。
调用有或没有_ids:
SELECT * FROM foofunc(1,'2012-1-1'::date);
实际上是一样的:
SELECT * FROM foofunc(1,'2012-1-1'::date,'{}'::int[]);
您必须确保该通话是明确的。如果您有另一个具有相同名称和两个参数的函数,Postgres可能不知道要选择哪个。显式铸造(像我演示)缩小。
来自另一个功能的呼叫:
CREATE FUNCTION foofuncwrapper(_param1 integer,_param2 date) RETURNS SETOF foobar AS $BODY$ DECLARE _ids int[] := '{1,2,3}'; BEGIN -- irrelevant stuff RETURN QUERY SELECT * FROM foofunc(_param1,_param2,_ids); END; $BODY$ LANGUAGE plgpsql;