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

Question

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;

Hungnam

Try setting default values for the last two parameters, or changing the order of parameters so the ones with default values are the last ones. – Airspeed

Answer 1

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;

Onesided

Great Stuff! Finally got cleared with all errors. Thank you so much. – Hungnam

Is there Ident_current() function in PostgreSQL? – Hungnam

@Meem: No, that looks like the Microsoft variant of currval(). – Onesided

Answer 2

Try

Create or replace function test(
out sno bigint, 
address varchar,
name varchar default null, 
city varchar default null, 
phonenumber varchar(20) default null)
   returns void as ..

Farmergeneral

Answer 3

Here you just need to reorder just it. In that case your problem will solved. default field must be at bottom.

Create or replace function test(
      out sno bigint, 
      ,address varchar
      ,name varchar default null
      ,city varchar default null
      ,phonenumber varchar(20) default null
)

Ytterbia