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;
