script.sql

ALTER SYSTEM SET max_connections = 180;


CREATE TABLE public.Cliente (
	Id smallint NOT NULL,
	Total int NOT NULL,
	Limite int NOT NULL,
	Versao int NOT NULL,
	CONSTRAINT "uq_Cliente_id" PRIMARY KEY (Id)
);

CREATE TABLE public.Transacao (
	Id int NOT NULL GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 100000 START 1 CACHE 1 NO CYCLE),
	Valor int NOT NULL,
	Descricao varchar(10) NOT NULL,
	Tipo varchar(1) NOT NULL,
	CriadoEm timestamptz NOT NULL,
	ClienteId smallint NOT NULL,
	CONSTRAINT fk_transacao_cliente_id FOREIGN KEY (ClienteId) REFERENCES public.Cliente(Id),
	CONSTRAINT "uq_transacao_id" PRIMARY KEY (Id)
);



INSERT INTO public.Cliente (Id, Total, Limite, Versao)
VALUES (1, 0, 100000, 0);

INSERT INTO public.Cliente (Id, Total, Limite, Versao)
VALUES (2, 0, 80000, 0);

INSERT INTO public.Cliente (Id, Total, Limite, Versao)
VALUES (3, 0, 1000000, 0);

INSERT INTO public.Cliente (Id, Total, Limite, Versao)
VALUES (4, 0, 10000000, 0);

INSERT INTO public.Cliente (Id, Total, Limite, Versao)
VALUES (5, 0, 500000, 0);



-- Functions
CREATE OR REPLACE FUNCTION creditar(
	ClienteId int4,
	Valor INT,
	Descricao varchar(10)
)
	RETURNS TABLE(
	    LinhaAfetada BOOLEAN,
	    _limite INT,
	    _total INT
	)
	LANGUAGE plpgsql AS
$func$
DECLARE
    updated_limite INT;
    updated_saldo INT;
BEGIN

    UPDATE public.Cliente SET Total = Total + Valor WHERE Id = ClienteId
		RETURNING Limite, Total INTO updated_limite, updated_saldo;

    INSERT INTO public.Transacao (ClienteId, Valor, Descricao, Tipo, CriadoEm) VALUES (ClienteId, Valor, Descricao, 'c', NOW());
    RETURN QUERY SELECT true, updated_limite, updated_saldo;

END;
$func$;

CREATE OR REPLACE FUNCTION debitar(
	ClienteId int4,
	Valor INT,
	Descricao varchar(10)
)
	RETURNS TABLE(
	    LinhaAfetada BOOLEAN,
	    _limite INT,
	    _total INT
	)
	LANGUAGE plpgsql AS
$func$
DECLARE
    updated_limite INT;
    updated_saldo INT;
BEGIN

    update public.Cliente SET Total = Total - @Valor WHERE Id = @ClienteId AND ((Total - @Valor) >= -Limite)
		RETURNING Limite, Total INTO updated_limite, updated_saldo;

    IF FOUND THEN
    	INSERT INTO public.Transacao (ClienteId, Valor, Descricao, Tipo, CriadoEm) VALUES (ClienteId, Valor, Descricao, 'd', NOW());
        RETURN QUERY SELECT true, updated_limite, updated_saldo;
    ELSE
        RETURN QUERY SELECT false, 0, 0;
    END IF;
END;
$func$;