On 12-1-2017 17:22, hamacker sirhamacker@... [firebird-support] wrote:

> hi All,
>
> I did a procedure that recompute all identity fields of a list with
> items separate by comma, but I need a method to check if table.field is
> identity or not. It´s a procedure of mainnitence.

For Firebird 3 identity fields, you can use (RF is RDB$RELATION_FIELDS):

CASE WHEN RF.RDB$IDENTITY_TYPE IS NULL THEN CAST('NO' AS VARCHAR(3))
ELSE CAST('YES' AS VARCHAR(3)) END AS IS_IDENTITY

BTW: "recompute all identity fields" sounds like an accident waiting to
happen. You should normally have no reason to reset this.

> My code:
> EXECUTE BLOCK
> returns (
> result_table varchar(255),
> result_idkey varchar(255),
> result_done varchar(1)) as
> declare variable source_list varchar(8192);
> declare variable l_item varchar(255);
> declare variable l_lastpos integer;
> declare variable l_nextpos integer;
> declare variable l_npos integer;
> declare variable l_idkey_max bigint = 0;
> declare variable l_is_identity integer;
> begin
> source_list='cv.id_cv,clientes.id_cliente,fornecedores.id_fornecedor';
> source_list = :source_list || ',';
> l_lastpos = 1;
> l_nextpos = position(',', :source_list, l_lastpos);
> while (:l_nextpos > 1) do
> begin
> l_item = substring(:source_list from :l_lastpos for :l_nextpos -
> :l_lastpos);
> result_done='N';
> if (trim(l_item)<>'') then
> begin
> l_npos = position('.', l_item);
> if (l_npos>0) then
> begin
> result_table = substring(:l_item from 1 for l_npos - 1);
> result_idkey = substring(:l_item from l_npos +1);
> -- se a tabela existir, então captura a numeracao mais alta e a
> transfere para o sequenciador
> if (exists(SELECT * FROM RDB$RELATIONS WHERE
> UPPER(RDB$RELATION_NAME) = UPPER(:result_table))) then
> begin
> execute statement 'select max('||:result_idkey||') FROM
> '||:result_table into :l_idkey_max;
> if (:l_idkey_max is null)
> then l_idkey_max=0;
> l_idkey_max=:l_idkey_max+1;
> l_is_identity=1;
> -- todo: How to identify if field of table is identity or not?
> if (l_is_identity>0) then
> begin
> execute statement 'ALTER TABLE '||:result_table||' ALTER
> COLUMN '||:result_idkey|| ' RESTART WITH '||CAST(:l_idkey_max as
> VARCHAR(8))||';';
> result_done = 'Y';
> end
> suspend;
> end
> end
> end
> l_lastpos = :l_nextpos + 1;
> l_nextpos = position(',', :source_list, l_lastpos);
> end
> end

--
Mark Rotteveel