_____ De: [email protected] [mailto:[email protected]] Em nome de <JUNIN> Trabalhando . . . %Segredos - Frejat% Enviada em: sexta-feira, 29 de maio de 2009 08:14 Para: Comunidade PostgreSQL Brasileira Assunto: Re: [pgbr-geral] validação de CPF em SQL Utilizo estas CREATE or replace FUNCTION fu_cnpj(a text) returns boolean as $$ declare k integer ; begin if length(a) <> 14 then return false ; end if ; k = 11 - mod(cast(substr(a, 1,1) as integer )*5 + cast(substr(a, 2,1) as integer )*4 + cast(substr(a, 3,1) as integer )*3 + cast(substr(a, 4,1) as integer )*2 + cast(substr(a, 5,1) as integer )*9 + cast(substr(a, 6,1) as integer )*8 + cast(substr(a, 7,1) as integer )*7 + cast(substr(a, 8,1) as integer )*6 + cast(substr(a, 9,1) as integer )*5 + cast(substr(a,10,1) as integer )*4 + cast(substr(a,11,1) as integer )*3 + cast(substr(a,12,1) as integer )*2,11); if k = 10 or k = 11 then k = 0 ; end if ; if k <> cast(substr(a,13,1) as integer) then return false ; end if ; k = 11 - mod(cast(substr(a, 1,1) as integer )*6 + cast(substr(a, 2,1) as integer )*5 + cast(substr(a, 3,1) as integer )*4 + cast(substr(a, 4,1) as integer )*3 + cast(substr(a, 5,1) as integer )*2 + cast(substr(a, 6,1) as integer )*9 + cast(substr(a, 7,1) as integer )*8 + cast(substr(a, 8,1) as integer )*7 + cast(substr(a, 9,1) as integer )*6 + cast(substr(a,10,1) as integer )*5 + cast(substr(a,11,1) as integer )*4 + cast(substr(a,12,1) as integer )*3 + cast(substr(a,13,1) as integer )*2,11) ; if k = 10 or k = 11 then k = 0 ; end if ; if k <> cast(substr(a,14,1) as integer) then return false ; end if ; return true ; end ; $$ LANGUAGE plpgsql IMMUTABLE RETURNS NULL ON NULL INPUT ; CREATE or replace FUNCTION fu_cpf(a text) returns boolean as $$ declare k integer ; begin if length(a) <> 11 then return false ; end if ; for k in 0 .. 9 loop if a = repeat(cast(k as char),11) then return false ; end if ; end loop ; k := 11- mod(cast(substr(a, 1,1) as integer )*10 + cast(substr(a, 2,1) as integer)* 9 + cast(substr(a, 3,1) as integer)* 8 + cast(substr(a, 4,1) as integer )* 7 + cast(substr(a, 5,1) as integer)* 6 + cast(substr(a, 6,1) as integer)* 5 + cast(substr(a, 7,1) as integer )* 4 + cast(substr(a, 8,1) as integer)* 3 + cast(substr(a, 9,1) as integer)* 2,11) ; if k = 10 or k = 11 then k = 0 ; end if ; if k <> cast(substr(a,10,1) as integer) then return false ; end if ; k := 11 - mod(cast(substr(a, 1,1) as integer )*11 + cast(substr(a, 2,1) as integer )*10 + cast(substr(a, 3,1) as integer )* 9 + cast(substr(a, 4,1) as integer )* 8 + cast(substr(a, 5,1) as integer )* 7 + cast(substr(a, 6,1) as integer )* 6 + cast(substr(a, 7,1) as integer )* 5 + cast(substr(a, 8,1) as integer )* 4 + cast(substr(a, 9,1) as integer )* 3 + cast(substr(a,10,1) as integer )* 2,11) ; if k = 10 or k = 11 then k = 0 ; end if ; if k <> cast(substr(a,11,1) as integer) then return false ; end if ; return true ; end ; $$ LANGUAGE plpgsql IMMUTABLE RETURNS NULL ON NULL INPUT ;
_______________________________________________ pgbr-geral mailing list [email protected] https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
