错误:一个带有默认值的输入参数也必须在Postgres中具有默认值

问题描述：

我试图将default的值设置为parameter列表中function内的变量，但出现错误:

I am trying to set default value to a variable within the function in parameter list but getting an error:

错误:带有默认值的输入参数后面的输入参数也必须具有默认值

ERROR: input parameters after one with a default value must also have defaults

示例:

 Create or replace function test(name varchar default null
                               , city varchar default null
                               , phonenumber varchar(20) default null
                               , out sno bigint, address varchar)
   returns void as
 $$
 Declare 
        phonenumber AS VarChar(20);
 Begin
        phonenumber : =phonenumber; 

    SELECT sno = MAX(ssno)+1 FROM emp;

    IF(sno IS NULL)  then
           sno=IDENT_CURRENT('emp')+1;
    end;

    raise info '%',name;
    raise info '%',city;
    raise info '%',phonenumber;
    raise info '%',address;

    insert into emp(ename,ecity,ephonenumber,eaddress)
    values(name,city,phonenumber,address);

 end;
 $$
 langauge plpgsql;

答

在您的示例中有很多不对的地方.或者更确切地说:在您的示例中，没什么.

Much is not right in your example. Or rather: not much is right in your example.

CREATE OR REPLACE FUNCTION f_test(
     name text = NULL
   , city text = NULL
   , phonenumber text = NULL
    ,address text = NULL
   , OUT sno bigint)
RETURNS void AS
 $func$
DECLARE 
    phonenumber AS VarChar(20);  -- would collide with parameter name
BEGIN
phonenumber := phonenumber;      -- nonsense

SELECT INTO sno  max(ssno) + 1 FROM emp;  -- SELECT INTO for assignment

IF sno IS NULL THEN
  sno := ident_current('emp') + 1;
END IF;

RAISE NOTICE '%, %, %, %', name, city, phonenumber, address;

INSERT INTO emp(ename, ecity, ephonenumber, eaddress)
VALUES (name, city, phonenumber, address);

END
$func$  LANGUAGE plpgsql;

要点

错误消息说明了一切:

Major points
- The error message speaks for itself:
  
  一个带有默认值的输入参数也必须具有默认值.
  
  input parameters after one with a default value must also have defaults.
  
  几乎是手册上的内容:
  
  带有默认值的参数之后的所有输入参数必须也具有默认值.
  
  All input parameters following a parameter with a default value must have default values as well.
- 将RETURNS void与OUT参数组合在一起没有任何意义.
- It wouldn't make sense to combine RETURNS void with OUT parameters.
  
  不要声明变量名与参数名冲突.在这里完全没用.
  
  Don't declare variable names colliding with parameter names. Completely useless here.
  
  plpgsql赋值运算符为:= ，而不是=.
  
  您通常不使用RAISE级别INFO.您需要使用NOTICE.
  
  You don't normally use the RAISE level INFO. You want NOTICE instead.
  
  SELECT不可能没有目标，您需要SELECT INTO.
  
  SELECT without target is not possible in plpgsql, you want SELECT INTO.
  
  IF以END IF终止，而不是以END终止.
  
  IF is terminated with END IF not with END.
  
  使用 COALESCE 替换您的IF语句.即使表为空，聚合函数也会返回一行.
  但是您也不需要.只需使用RETURNING子句直接返回新的ID:
  
  Use COALESCE to replace your IF statement. Aggregate functions return a row even if the table is empty.
  But you don't need that either. Just use the RETURNING clause to return the new id directly:
```
CREATE OR REPLACE FUNCTION f_test(
     name text = NULL
   , city text = NULL
   , phonenumber text = NULL
   , address text = NULL
   , OUT sno bigint)
AS
$func$
BEGIN
RAISE NOTICE '%, %, %, %', name, city, phonenumber, address;

INSERT INTO emp(ename, ecity, ephonenumber, eaddress)
VALUES (name, city, phonenumber, address)
RETURNING ssno
INTO   sno;             -- Assuming you want to return autoincremented id 

END
$func$  LANGUAGE plpgsql;
```

错误:一个带有默认值的输入参数也必须在Postgres中具有默认值

示例:

要点

Major points

相关推荐