DROP PROCEDURE IF EXISTS sp_generateidforentity;
DROP FUNCTION IF EXISTS sp_generateidforentity;

CREATE PROCEDURE sp_generateidforentity
(
	"EntName" varchar(256)
,	"FieldName" varchar(256) = 'PrimaryKey'
,	"Count" integer = 1
,   INOUT _rc int = -1
)
AS
$BODY$
Declare	_msg text;
Begin
	_msg := 'Ошибка генерации id для ' || "EntName";

	--LOCK TABLE T_ENTITY_IDS IN ACCESS EXCLUSIVE MODE;
	select id 
	from T_ENTITY_IDS
	into _rc 
	where EntName = "EntName" 
	and FieldName = "FieldName"
	for update;

	if _rc is null  
	then  
		insert into T_ENTITY_IDS(id, EntName, FieldName) 
		values(1 + "Count", "EntName", "FieldName");
		_rc := 1;
	else   
		update T_ENTITY_IDS 
		set id = id + "Count" 
		where EntName = "EntName" 
		and FieldName = "FieldName";
	end if;
	return;
Exception
	WHEN Others 
	THEN Raise Exception Using Message = _msg;
_rc := -1;
Return;
End;
$BODY$
  LANGUAGE plpgsql;
--ALTER PROCEDURE sp_generateidforentity(character varying, character varying, integer, integer) OWNER TO postgres;
