do $$
begin
	if exists 
	(
		select * from pg_proc p
		where p.proname = 'sp_generatenumber'
	)
	then
		drop function public.sp_generatenumber(int, varchar, int);
	end if;
end;
$$ language plpgsql;
-- =============================================
-- Author:  (C) Vitacard System 2009
-- Create date: 2009-08-05
-- Description: Генерирует номер
-- =============================================
CREATE or Replace Function public.sp_GenerateNumber
(
	"EntityID" int
,	"NumberName" varchar(64)
,	"Count" int default 1 
)
RETURNS int As 
$$
Declare	EntityIDVar Alias for $1;
	NumberNameVar Alias for $2;
	msg text;
	NextNumberVar int;
Begin
	msg := 'Ошибка генерации номера.';

	--LOCK TABLE ISZL.T_GEN_NUMBERS IN ACCESS EXCLUSIVE MODE;
	
	select t.NextNumber 
	from ISZL.T_GEN_NUMBERS t
	into NextNumberVar
	where t.EntityID = EntityIDVar 
	and t.NumberName like NumberNameVar
	for update;

	if NextNumberVar is null 
	then
		NextNumberVar := 1;
		insert into ISZL.T_GEN_NUMBERS (GenNumbersID, EntityID, NumberName, NextNumber) 
		select gen_random_uuid(), EntityIDVar, NumberNameVar, NextNumberVar + "Count" - 1;
	else
		NextNumberVar := NextNumberVar + 1;
		update ISZL.T_GEN_NUMBERS 
		set NextNumber = NextNumberVar + "Count" - 1 
		where EntityID = EntityIDVar 
		and NumberName = NumberNameVar;
	end if;
	return NextNumberVar;
Exception
	WHEN Others 
	THEN Raise Exception Using Message = msg;
		return -1;	
End;
$$ Language plpgsql;