DROP PROCEDURE IF EXISTS sp_generatenumber;
-- =============================================
-- Author:  (C) Vitacard System 2009
-- Create date: 2009-08-05
-- Description: Генерирует номер
-- =============================================
CREATE PROCEDURE sp_generatenumber
(
	"EntityID" integer
,	"NumberName" varchar(64)
,	"count" integer DEFAULT 1
,   INOUT _val integer DEFAULT -1
)
LANGUAGE plpgsql AS
$proc$
Declare	_msg text;
BEGIN
	_msg := 'Ошибка генерации номера.';

	--LOCK TABLE akuz.T_GEN_NUMBERS IN ACCESS EXCLUSIVE MODE;
	select t.NextNumber 
	from akuz.T_GEN_NUMBERS t
	into _val
	where t.EntityID = "EntityID" 
	and t.NumberName like "NumberName"
	for update;
	
	if _val is null 
	then
		_val := 1;
		insert into akuz.T_GEN_NUMBERS (GenNumbersID, EntityID, NumberName, NextNumber) 
		select uuid_generate_v4(), "EntityID", "NumberName", _val + "count" - 1;
	else
		_val := _val + 1;
		update akuz.T_GEN_NUMBERS 
		set NextNumber = _val + "count" - 1 
		where EntityID = "EntityID"
		and NumberName = "NumberName";
	end if;
	return;
Exception
	WHEN Others 
	THEN Raise Exception Using Message = _msg;
	_val := -1;
return;
End
$proc$;