Next Employee ID

Hi,

I've been trying to find out what's wrong with my function:

create function Next_Employee_ID() returns text AS '
declare       Emp             as RECORD;       CurYear         as text;       NextEmpYrNo     as int;

begin       -- get current year       CurYear := substr( datetime_text(now()), 21,4);
       -- read latest employee.id       Select max(id) into Emp from Employee               Where id like CurYear ||
''%'';
       if not found then               -- create new sequence starting from "current year" +    -- "-001"::text
     return CurYear || ''-001'';       else               -- extract last 3 chars -- convert it to int -- add 1
     NextEmpYrNo := substr(Emp.max, 6, 3)::int + 1;               return CurYear || ''-'' || NextEmpYrNo::text;
endif;
 
end;' language 'plpgsql';


I have an employee table who have an field named id of type varchar(8);


herbert