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 sp_generatenumber
(
	"EntityID" integer
,	"NumberName" varchar(64)
,	"count" integer DEFAULT 1
)
RETURNS integer AS
$BODY$
Declare	_msg text;
	_NextNumber int;
Begin
	_msg := 'Ошибка генерации номера.';

	--LOCK TABLE akuz.T_GEN_NUMBERS IN ACCESS EXCLUSIVE MODE;
	select t.NextNumber 
	from akuz.T_GEN_NUMBERS t
	into _NextNumber
	where t.EntityID = "EntityID" 
	and t.NumberName like "NumberName"
	for update;
	
	if _NextNumber is null 
	then
		_NextNumber := 1;
		insert into akuz.T_GEN_NUMBERS (GenNumbersID, EntityID, NumberName, NextNumber) 
		select uuid_generate_v4(), "EntityID", "NumberName", _NextNumber + "count" - 1;
	else
		_NextNumber := _NextNumber + 1;
		update akuz.T_GEN_NUMBERS 
		set NextNumber = _NextNumber + "count" - 1 
		where EntityID = "EntityID"
		and NumberName = "NumberName";
	end if;
	return _NextNumber;
Exception
	WHEN Others 
	THEN Raise Exception Using Message = _msg;
return -1;
End;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION sp_generatenumber(integer, varchar(64), integer)
  OWNER TO postgres;