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