Search code examples
postgresqlplpgsqldefault-value

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


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

Example:

 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;

Solution

  • 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.

      That's almost what the manual has on that:

      All input parameters following a parameter with a default value must have default values as well.

    • It wouldn't make sense to combine RETURNS void with OUT parameters.

    • Don't declare variable names colliding with parameter names. Completely useless here.

    • The plpgsql assignment operator is :=, not =.

    • You don't normally use the RAISE level INFO. You want NOTICE instead.

    • SELECT without target is not possible in plpgsql, you want SELECT INTO.

    • IF is terminated with END IF not with END.

    Simplify further

    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;