CREATE OR REPLACE FUNCTION public.controle_prod_p()
  RETURNS trigger AS
$BODY$
    DECLARE id uuid;
    DECLARE usuario varchar(255);
    DECLARE query text;
    DECLARE test boolean;
    DECLARE bd varchar(255);

    BEGIN
	SELECT current_user into usuario;
	SELECT current_database() into bd;
	SELECT md5(random()::text || clock_timestamp()::text)::uuid INTO id;

	IF TG_OP = 'INSERT' THEN
		query := 'INSERT INTO public.controle(uuid,data,operacao,usuario,classe,database, objeto, historico)
		VALUES('|| '''' || id ||'''' ||','||'''' || NOW() ||'''' ||','||'''' || TG_OP ||'''' ||',' ||'''' || usuario ||'''' || ',' ||'''' || TG_TABLE_NAME ||'''' || ',' ||'''' || bd ||'''' || ',' ||'''' ||  row_to_json(NEW) ||'''' || ',NULL);';
		NEW.controle_id = id;

	END IF;

	IF TG_OP = 'UPDATE' THEN

		query := 'INSERT INTO public.controle(uuid,data,operacao,usuario,classe,database)
		VALUES('|| '''' || id ||'''' ||','||'''' || NOW() ||'''' ||','||'''' || TG_OP ||'''' ||',' ||'''' || usuario ||'''' || ',' ||'''' || TG_TABLE_NAME ||'''' || ',' ||'''' || bd ||'''' || ',' ||'''' ||  row_to_json(NEW) ||'''' || ',' ||'''' ||  OLD.controle_id ||'''' || ');';
	  NEW.controle_id = id;
	END IF;

	IF TG_OP = 'DELETE' THEN

		query := 'DELETE FROM public.controle WHERE uuid=' ||'''' || OLD.controle_id ||''''||'; INSERT INTO public.controle(uuid,data,operacao,usuario,classe,database)
		VALUES('|| '''' || OLD.controle_id ||'''' ||','||'''' || NOW() ||'''' ||','||'''' || TG_OP ||'''' ||',' ||'''' || usuario ||'''' || ',' ||'''' || TG_TABLE_NAME ||'''' || ',' ||'''' || bd ||'''' || ');';

	END IF;

	SELECT 'conn1' = ANY (dblink_get_connections()) INTO test;
	IF test THEN
		PERFORM dblink('conn1',query);
	ELSE
		PERFORM dblink_connect_u('conn1', 'dbname=controle_micro port=5434 hostaddr=10.25.163.9 user=cmicro password=cmicro');
		PERFORM dblink('conn1',query);
	END IF;

	IF TG_OP = 'DELETE' THEN
		RETURN OLD;
	ELSE
		RETURN NEW;
	END IF;

    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.controle_prod_p()
OWNER TO postgres;