postgresql 我可以让 plpgsql 函数在不使用变量的情况下返回一个整数吗？

Can I make a plpgsql function return an integer without using a variable?

提问by danidacar

Something like this:

像这样的东西：

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;

I would like to avoid a DECLAREjust for this.

我想避免一个DECLAREjust for this。

回答by Erwin Brandstetter

Yes you can. There are a number of ways.

是的你可以。有多种方法。

1) RETURN (SELECT ...)

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) Use an OUTor INOUTparameter

2) 使用OUTorINOUT参数

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;

More in the manual here.

此处的手册中有更多内容。

3) (Ab)use INparameter

3) (Ab)使用IN参数

Since Postgres 9.0you can also use input parametersas variables. The release notes for 9.0:

从Postgres 9.0 开始，您还可以使用输入参数作为变量。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:
   --  := col1 FROM TABLE WHERE id = ;
   -- RETURN ;
END
$func$  LANGUAGE plpgsql;

With the last ones you do usea variable implicitly, but you don't have to DECLAREit explicitly (as requested).

对于最后一个，您确实隐式地使用了一个变量，但您不必DECLARE显式地使用它（根据要求）。

4) Use a DEFAULTvalue with an INOUTparameter

4) 使用DEFAULT带INOUT参数的值

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 = 123is short notation for INOUT _col1 integer DEFAULT 123. More:

INOUT _col1 integer = 123是 的简写INOUT _col1 integer DEFAULT 123。更多的：

• The forgotten assignment operator "=" and the commonplace ":="

• 被遗忘的赋值运算符“=”和常见的“:=”

5) Use a plain SQL functioninstead

5）使用普通的SQL函数代替

CREATE OR REPLACE FUNCTION get(_param_id integer)
  RETURNS integer AS
$func$
   SELECT col1 FROM TABLE WHERE id = _param_id;
   -- use positional reference  instead of param name in Postgres 9.1 or older
$func$  LANGUAGE sql;