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 VCLib.T_GEN_NUMBERS IN ACCESS EXCLUSIVE MODE;
	
--	select t.NextNumber 
--	from VCLib.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 VCLib.T_GEN_NUMBERS (GenNumbersID, EntityID, NumberName, NextNumber) 
--		select uuid_generate_v1(), EntityIDVar, NumberNameVar, NextNumberVar + "Count" - 1;
--	else
--		NextNumberVar := NextNumberVar + 1;
--		update VCLib.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;