这样的事情
CREATE OR REPLACE FUNCTION get(param_id integer) RETURNS integer AS $BODY$ BEGIN SELECT col1 FROM TABLE WHERE id = param_id; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100;
我想为此避免一个DECLARE.
是的你可以.有很多方法.
1)RETURN(SELECT …)
CREATE OR REPLACE FUNCTION get(_param_id integer) RETURNS integer AS $func$ BEGIN RETURN (SELECT col1 FROM TABLE WHERE id = _param_id); END $func$LANGUAGE plpgsql;
2)使用OUT或INOUT参数
CREATE OR REPLACE FUNCTION get(_param_id integer,OUT _col1 integer) RETURNS integer AS -- "RETURNS integer" is optional in this case $func$ BEGIN SELECT INTO _col1 col1 FROM TABLE WHERE id = _param_id; -- also valid,but not as clean: -- _col1 := col1 FROM TABLE WHERE id = _param_id; END $func$LANGUAGE plpgsql;
阅读更多in the manual here.
3)(Ab)使用IN参数
由于Postgresql 9.0还可以使用输入参数作为变量.我引用release notes for：
An input parameter now acts like a local variable initialized to the
passed-in value.
人机工程学：
CREATE OR REPLACE FUNCTION get(_param_id integer) RETURNS integer AS $func$ BEGIN SELECT INTO _param1 col1 FROM TABLE WHERE id = _param1; RETURN _param1; -- Also possible,but discouraged: -- $1 := col1 FROM TABLE WHERE id = $1; -- RETURN $1; END $func$LANGUAGE plpgsql;
最后一个你确实使用一个变量,严格来说,但你不必另外声明.
4)使用带有INOUT参数的DEFAULT值
这是一个特殊情况.
CREATE OR REPLACE FUNCTION get(_param_id integer,INOUT _col1 integer = 123) RETURNS integer AS $func$ BEGIN -- You can set _col1 to some other value: -- SELECT INTO _col1 col1 FROM TABLE WHERE id = _param_id; -- If you don't,123 will be returned. END $func$LANGUAGE plpgsql;
5)改用纯SQL function
CREATE OR REPLACE FUNCTION get(_param_id integer) RETURNS integer AS $func$ SELECT col1 FROM TABLE WHERE id = _param_id; -- use $1 in Postgres 9.1 or older $func$ LANGUAGE sql;
