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
