是否可以使plpgsql函数在不使用变量的情况下返回整数? [英] Can I make a plpgsql function return an integer without using a variable?
本文介绍了是否可以使plpgsql函数在不使用变量的情况下返回整数?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
类似这样的东西:
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;
为此,我想避免使用DECLARE
.
推荐答案
可以.有很多方法.
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
参数
2) Use an OUT
or INOUT
parameter
CREATE OR REPLACE FUNCTION get(_param_id integer, OUT _col1 integer)
-- RETURNS integer -- "RETURNS integer" is optional noise in this case
AS
$func$
BEGIN
SELECT INTO _col1 col1 FROM TABLE WHERE id = _param_id;
-- also valid, but discouraged:
-- _col1 := col1 FROM TABLE WHERE id = _param_id;
END
$func$ LANGUAGE plpgsql;
自 Postgres 9.0 起,您还可以使用输入参数作为变量. 9.0版的发行说明:
Since Postgres 9.0 you can also use input parameters as variables. The release notes for 9.0:
输入参数现在的作用就像是初始化为传入值的局部变量.
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;
对于最后一个变量,您可以隐式使用一个变量,但不必(按要求)显式地DECLARE
使用它.
With the last ones you do use a variable implicitly, but you don't have to DECLARE
it explicitly (as requested).
这是一种特殊情况.函数主体可以为空.
This is a bit of a special case. The function body can be empty.
CREATE OR REPLACE FUNCTION get(_param_id integer, INOUT _col1 integer = 123)
RETURNS integer AS
$func$
BEGIN
-- You can assign some (other) value to _col1:
-- SELECT INTO _col1 col1 FROM TABLE WHERE id = _param_id;
-- If you don't, the DEFAULT 123 will be returned.
END
$func$ LANGUAGE plpgsql;
INOUT _col1 integer = 123
是INOUT _col1 integer DEFAULT 123
的缩写.更多:
CREATE OR REPLACE FUNCTION get(_param_id integer)
RETURNS integer AS
$func$
SELECT col1 FROM TABLE WHERE id = _param_id;
-- use positional reference $1 instead of param name in Postgres 9.1 or older
$func$ LANGUAGE sql;
这篇关于是否可以使plpgsql函数在不使用变量的情况下返回整数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文