Efetuando exclusão segura de registro de uma tabela com store function

Efetuando exclusão segura de registro de uma tabela com store function

--
-- Nome Artefato/Programa..: exclusao.sql
-- Instituicao.............:
-- Autor(es)...............: O Peregrino (emersonhermann at gmail.com)
-- Data Inicio ............: 29/06/2010
-- Data Atual..............: 29/06/2010
-- Versao..................: 0.01
-- Linguagem...............: PL/pgSQL
-- Compilador/Interpretador: PostgreSql
-- Sistemas Operacionais...: Linux/Windows
-- SGBD....................: PostgreSql 8.x
-- Kernel..................: Nao informado!
-- Finalidade..............: Efetuar exclusao segura de um registro
-- OBS1....................: Efetuar exclusao segura de um registro

DROP FUNCTION IF EXISTS public.exclusao(INTEGER);
CREATE OR REPLACE FUNCTION public.exclusao(INTEGER)
RETURNS BOOLEAN AS $$

BEGIN

    DELETE FROM tabela WHERE campo_chave=$1;

    IF FOUND THEN

        RAISE NOTICE 'O registro % foi excluido.',$1;
        RETURN TRUE;

    END IF;

    RAISE NOTICE 'O registro % não foi encontrado.',$1;
    RETURN FALSE;

END;
$$ LANGUAGE 'plpgsql' RETURNS NULL ON NULL INPUT
;


--polimorfismo
DROP FUNCTION IF EXISTS public.exclusao(esquema_tabelax VARCHAR(255), campox VARCHAR(255), idx INTEGER);
CREATE OR REPLACE FUNCTION public.exclusao(esquema_tabelax VARCHAR(255), campox VARCHAR(255), idx INTEGER)
RETURNS BOOLEAN AS $$

DECLARE

  ds_comando   TEXT;
  deletado     BIGINT;

BEGIN

     IF (esquema_tabelax IS NULL) THEN
          RAISE EXCEPTION 'ESQUEMA TABELA COM VALOR NULO!'; -- caso o parametro venha nulo
     END IF;

     IF (campox IS NULL) THEN
          RAISE EXCEPTION 'CAMPO COM VALOR NULO!'; -- caso o parametro venha nulo
     END IF;

     IF (idx IS NULL) THEN
          RAISE EXCEPTION 'ID COM VALOR NULO!'; -- caso o parametro venha nulo
     END IF;


     ds_comando     := 'DELETE FROM ' || esquema_tabelax || ' WHERE ' || campox || '=' || idx;
    
    EXECUTE ds_comando;

    GET DIAGNOSTICS deletado = ROW_COUNT;
   
    IF deletado > 0 THEN

        RAISE NOTICE '%', ds_comando;
        RAISE NOTICE 'Total de registro(s) deletado(s): %',deletado;
        RETURN TRUE;

    END IF;
   
     RAISE NOTICE '%', ds_comando;
     RAISE NOTICE 'Total de registro(s) deletado(s): %',deletado;
    RAISE NOTICE 'O registro % não foi encontrado. NADA FOI DELETADO!!.',idx;
    RETURN FALSE;

END;
$$ LANGUAGE 'plpgsql' RETURNS NULL ON NULL INPUT
;


DROP TABLE IF EXISTS "E PRA SER DELETADO";
CREATE TABLE "E PRA SER DELETADO"
(
     ID INTEGER
);

INSERT INTO "E PRA SER DELETADO" (id) VALUES (6);
INSERT INTO "E PRA SER DELETADO" (id) VALUES (6);
INSERT INTO "E PRA SER DELETADO" (id) VALUES (6);

SELECT * FROM "E PRA SER DELETADO";

DROP TABLE IF EXISTS apague_me;
CREATE TABLE apague_me
(
     ID INTEGER
);
INSERT INTO apague_me (id) VALUES (1);
INSERT INTO apague_me (id) VALUES (2);
INSERT INTO apague_me (id) VALUES (3);
INSERT INTO apague_me (id) VALUES (4);
INSERT INTO apague_me (id) VALUES (5);
INSERT INTO apague_me (id) VALUES (6);
INSERT INTO apague_me (id) VALUES (6);
INSERT INTO apague_me (id) VALUES (6);
INSERT INTO apague_me (id) VALUES (7);

--DELETE FROM public.apague_me  WHERE id = 6;

SELECT * FROM apague_me;

--testes
--deletar pelos parametros
SELECT EXCLUSAO('public.apague_me','ID',6); --ok

--delete pelos parametros com null
SELECT EXCLUSAO('public.apague_me','ID',NULL); --ok
SELECT EXCLUSAO('public.apague_me',NULL,NULL); --ok
SELECT EXCLUSAO(NULL,NULL,NULL); --ok

--tabela ou campos inexistentes
SELECT EXCLUSAO('public.apague_me','idx',6); --ok
SELECT EXCLUSAO('public.apague_mex','id',6); --ok

--com transacao
BEGIN; SELECT EXCLUSAO('public.apague_me','id',6); --ok
--ROLLBACK;
--COMMIT;

--sem parametros
SELECT EXCLUSAO('public.apague_me','ID'); --ok

--sql injection
SELECT EXCLUSAO('drop database banco;','ID',6); --ok

--case sensitve
SELECT EXCLUSAO('"E PRA SER DELETADO"','ID',6); --ok
SELECT EXCLUSAO('public."E PRA SER DELETADO"','ID',6); --ok