Subject Re: [firebird-support] How to identify if field of table is identity or not?
Author hamacker
Post date 2017-01-12T16:52:47Z
just be careful, shits happens ;)

Now, its done:

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='';
  if (:source_list='') then
  begin
    for select rel.rdb$relation_name, rf.rdb$field_name
    from rdb$relations rel
    inner join rdb$relation_fields rf on (rf.rdb$relation_name=rel.rdb$relation_name)
    where rf.rdb$identity_type is not null
    into :result_table, :result_idkey do
    begin
      source_list=:source_list||:result_table||'.'||:result_idkey||',';
    end
  end
  if (substring(:source_list from octet_length(:source_list) for 1)<>',')
    then 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 1 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;
          -- identificando se o campo é identity ou não
          l_is_identity=0;
          if (exists(
                select 1 from rdb$relations rel
                inner join rdb$relation_fields rf on (rf.rdb$relation_name=rel.rdb$relation_name)
                where
                  (UPPER(rel.rdb$relation_name) = UPPER(:result_table)) and
                  (UPPER(rf.rdb$field_name) = UPPER(:result_idkey)) and
                  (rf.rdb$identity_type is not null)
             )) then l_is_identity=1;
          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


2017-01-12 14:33 GMT-02:00 Mark Rotteveel mark@... [firebird-support] <firebird-support@yahoogroups.com>:
 


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