Código fonte do Cálculo de sequencia de fatorial e fibonacci em PL/PgSQL

Coloquei esse código aqui, mais pra não esquecer como concateno duas string no PL/PgSQL, estava colocando select onde não existia ...




O código do fatorial e fibonacci foi melhorado, já que fibonacci demora muito para ser executado e fatorial gera números que crescem exponencialmente ...


select CPF_formatar(gerar_CPF()) ;

select *,CPF_formatar(gerar_CPF()) , CPF_Validar(gerar_CPF()) from pacientes ;


SELECT CPF_formatar('05387524910');
SELECT fibonacci(1);
SELECT fibonacci(2);
SELECT fibonacci(3);
SELECT fibonacci(4);
SELECT fibonacci(5);
SELECT fibonacci(6);;
SELECT seqfibonacci(30);
SELECT seqfatorial(60);
SELECT seqfCPFsValidos(4);
SELECT CPF_Validar1(31141593386);
SELECT CPF_Validar1(05387524910);
SELECT gerar_CPF2();
/*----------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------*/
--drop function gerar_CPF2();
CREATE OR REPLACE FUNCTION gerar_CPF2() RETURNS bigint AS $bb$
BEGIN
return cast( gerar_CPF() as bigint);
END;
$bb$
LANGUAGE PLPGSQL;
/*----------------------------------------------------------------------------------------*/
CREATE OR REPLACE FUNCTION CPF_Validar1(bigint) RETURNS integer AS $bb$
BEGIN
if $1 is NULL or $1<=0 or $1>=99999999999 THEN RAISE EXCEPTION ' Erro! O valor % e invalido pra cpf($1<=0 or $1>=99999999999 )!', $1; RETURN NULL;
end if;
return CPF_Validar(cast($1 as varchar(11)));
END;
$bb$
LANGUAGE PLPGSQL;
/*----------------------------------------------------------------------------------------*/
CREATE OR REPLACE FUNCTION seqfCPFsValidos(integer) RETURNS text AS
$BODY$ DECLARE
arg text;
i12 integer;
BEGIN
IF $1 IS NULL OR $1 <= 0 THEN
RAISE NOTICE '% Valor Invalido!', $1;
RETURN NULL;
END IF;
arg='';
i12:=1;
WHILE i12<=$1 LOOP
arg := arg||CPF_formatar(gerar_CPF())||' ';
i12:=i12+1;
END LOOP;
return arg;
END;
$BODY$
LANGUAGE 'plpgsql' ;
/*----------------------------------------------------------------------------------------*/

/*----------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------*/
-- Function: seqfatorial(bigint);

-- DROP FUNCTION seqfatorial(bigint);
CREATE OR REPLACE FUNCTION seqfatorial(bigint)
RETURNS text AS
$BODY$ DECLARE
arg text;
i12 bigint;
BEGIN
IF $1 IS NULL OR $1 <= 0 THEN
RAISE NOTICE '% Valor Invalido!', $1;
RETURN NULL;
END IF;
arg='';
i12:=1;
WHILE i12<=$1 LOOP
--select arg||fibonacci(i12);
arg := arg|| fatorial(i12)||' ';
i12:=i12+1;
END LOOP;
return arg;
END;
$BODY$
LANGUAGE 'plpgsql' ;
/*----------------------------------------------------------------------------------------*/
-- Function: fatorial(bigint)

-- DROP FUNCTION fatorial(bigint);

CREATE OR REPLACE FUNCTION fatorial(bigint)
RETURNS numeric(1000,0) AS
$BODY$ DECLARE
arg INTEGER;
BEGIN
arg := $1;
IF arg IS NULL OR arg < 0 THEN
RAISE NOTICE '% Valor Invalido!', $1;
RETURN NULL;
END IF;
IF arg = 0 THEN
RETURN 0;
END IF;
IF arg = 1 THEN
RETURN 1;

ELSE
IF arg = 2 THEN
RETURN 2;
END IF;
IF arg = 3 THEN
RETURN 6;
END IF;
DECLARE
next_value numeric(1000,0);
BEGIN
next_value := fatorial(arg -1) * arg ;
RETURN next_value;
END;

END IF;
END;
$BODY$
LANGUAGE 'plpgsql' ;
/*----------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------*/
-- Function: seqfibonacci(bigint);

-- DROP FUNCTION seqfibonacci(bigint);
CREATE OR REPLACE FUNCTION seqfibonacci(bigint)
RETURNS text AS
$BODY$ DECLARE
arg text;
i12 bigint;
BEGIN
IF $1 IS NULL OR $1 < 0 THEN
RAISE NOTICE '% Valor Invalido!', $1;
RETURN NULL;
END IF;
arg='';
i12:=0;
WHILE i12<=$1 LOOP
--select arg||fibonacci(i12);
arg := arg|| fibonacci(i12)||' ';
i12:=i12+1;
END LOOP;
return arg;
END;
$BODY$
LANGUAGE 'plpgsql' ;
/*----------------------------------------------------------------------------------------*/
-- Function: fibonacci(bigint)
-- Function: fibonacci(bigint)

-- DROP FUNCTION fibonacci(bigint);

CREATE OR REPLACE FUNCTION fibonacci(bigint)
RETURNS bigint AS
$BODY$ DECLARE
arg bigint;
BEGIN
arg := $1;
IF arg IS NULL OR arg < 0 THEN
RAISE NOTICE '% Valor Invalido!', $1;
RETURN NULL;
END IF;
IF arg = 1 THEN
RETURN 1;

ELSE
IF arg = 2 THEN
RETURN 1;
END IF;
IF arg = 0 THEN
RETURN 0;
END IF;

IF arg = 3 THEN
RETURN 2;
END IF;
IF arg = 4 THEN
RETURN 3;
END IF;
IF arg = 5 THEN
RETURN 5;
END IF;
IF arg = 6 THEN
RETURN 8;
END IF;
IF arg = 7 THEN
RETURN 13;
END IF;
IF arg = 8 THEN
RETURN 21;
END IF;
IF arg = 9 THEN
RETURN 34;
END IF;
IF arg = 10 THEN
RETURN 55;
END IF;
IF arg = 11 THEN
RETURN 89;
END IF;
IF arg = 12 THEN
RETURN 144;
END IF;
IF arg = 13 THEN
RETURN 233;
END IF;
IF arg = 14 THEN
RETURN 377;
END IF;
IF arg = 15 THEN
RETURN 610;
END IF;
IF arg = 16 THEN
RETURN 987;
END IF;
IF arg = 17 THEN
RETURN 1597;
END IF;
IF arg = 18 THEN
RETURN 2584;
END IF;
IF arg = 19 THEN
RETURN 4181;
END IF;
IF arg = 20 THEN
RETURN 6765;
END IF;
IF arg = 21 THEN
RETURN 10946 ;
END IF;
IF arg = 22 THEN
RETURN 17711 ;
END IF;
IF arg = 23 THEN
RETURN 28657 ;
END IF;
IF arg = 24 THEN
RETURN 46368 ;
END IF;
IF arg = 25 THEN
RETURN 75025 ;
END IF;
IF arg = 26 THEN
RETURN 121393 ;
END IF;
IF arg = 27 THEN
RETURN 196418 ;
END IF;
IF arg = 28 THEN
RETURN 317811 ;
END IF;
IF arg = 29 THEN
RETURN 514229 ;
END IF;
IF arg = 30 THEN
RETURN 832040 ;
END IF;
IF arg = 31 THEN
RETURN 1346269 ;
END IF;
IF arg = 32 THEN
RETURN 2178309 ;
END IF;
IF arg = 33 THEN
RETURN 3524578 ;
END IF;
IF arg = 34 THEN
RETURN 5702887 ;
END IF;
DECLARE
next_value bigint;
BEGIN
next_value := fibonacci(arg -2) + fibonacci(arg -1) ;
RETURN next_value;
END;

END IF;
END;
$BODY$
LANGUAGE 'plpgsql' ;
/*----------------------------------------------------------------------------------------*/