/*-- SEQ 0001 --*/
/*-- IGNORE ERRO --*/
DROP FUNCTION ExecAlteraTipoColuna (VARCHAR,VARCHAR,VARCHAR) CASCADE;

/*-- SEQ 0001 --*/
CREATE OR REPLACE FUNCTION ExecAlteraTipoColuna (VARCHAR,VARCHAR,VARCHAR)
RETURNS VARCHAR AS '
DECLARE
  pTabela   ALIAS FOR $1 ;
  pCampo    ALIAS FOR $2 ;
  pNovoTipo ALIAS FOR $3 ;
  nVer8 integer;
  cComando VARCHAR(255);
   
  cur_views CURSOR (pTabela_ VARCHAR, pCampo_ VARCHAR)  FOR 
    SELECT ''drop view ''||viw.relname||'' cascade;'' AS comando
      FROM pg_attribute att inner join pg_depend dep on dep.refobjsubid = att.attnum 
                            inner join pg_rewrite rew on rew.oid=dep.objid 
                            inner join pg_class viw on viw.oid=rew.ev_class
                            inner join pg_class tab on dep.refobjid=tab.oid and att.attrelid=tab.oid
                            left  join pg_namespace scm  ON scm.oid = tab.relnamespace
         WHERE dep.refclassid=1259 
           AND att.attname=lower(pCampo)
           AND dep.classid=''pg_rewrite''::regclass
           AND rew.rulename=''_RETURN'' 
           AND tab.relname=lower(pTabela)
           AND scm.nspname = current_schema();
         
BEGIN  

   select instr(version(),''PostgreSQL 8'') into nVer8;
   
   if nVer8=1 then 
      Open cur_views(pTabela, pCAMPO);
      LOOP
         FETCH cur_views INTO cCOMANDO;
         EXIT WHEN NOT FOUND;
         RAISE NOTICE ''Executando %'',cCOMANDO;
         EXECUTE cCOMANDO;
      END LOOP;
      Close cur_views;
       
      EXECUTE ''ALTER TABLE ''||pTabela||'' alter column ''||pCampo||'' type ''||pNovoTipo||'';'';
   else
      EXECUTE AlteraTipoColuna ($1,$2,$3);       
   end if;
   RETURN NULL;
END;
' language 'plpgsql';



SELECT ExecAlteraTipoColuna('TT_SQL','RELORI','VARCHAR(2)');
SELECT ExecAlteraTipoColuna('TT_SQL','RELDES','VARCHAR(2)');

UPDATE TT_SQL SET RELORI = TRIM(RELORI);
UPDATE TT_SQL SET RELDES = TRIM(RELDES);