我可以让 plpgsql 函数在不使用变量的情况下返回一个整数吗? [英] Can I make a plpgsql function return an integer without using a variable?

问题描述

像这样:

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 只是为了这个.

I would like to avoid a DECLARE just for this.

推荐答案

是的，你可以.有很多方法.

Yes you can. There are a number of ways.

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 的缩写.更多:

INOUT _col1 integer = 123 is short notation for INOUT _col1 integer DEFAULT 123. More:

• 被遗忘的赋值运算符="和司空见惯的:="

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屋！