init.sql

BEGIN;

CREATE UNLOGGED TABLE IF NOT EXISTS CLIENTS (
    ID SERIAL PRIMARY KEY,
    ACCOUNT_LIMIT INTEGER NOT NULL DEFAULT 0,
    BALANCE INTEGER NOT NULL DEFAULT 0
);

CREATE UNLOGGED TABLE IF NOT EXISTS TRANSACTIONS (
    ID SERIAL PRIMARY KEY,
    CLIENT_ID INTEGER NOT NULL,
    VALUE INTEGER NOT NULL,
    TYPE VARCHAR(1) NOT NULL,
    DESCRIPTION VARCHAR(10) NOT NULL,
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IDX_TRANSACTIONS ON TRANSACTIONS (ID DESC);
CREATE INDEX IDX_TRANSACTIONS_CLIENT_ID ON TRANSACTIONS (CLIENT_ID);

INSERT INTO CLIENTS (ACCOUNT_LIMIT, BALANCE) VALUES (100000, 0);
INSERT INTO CLIENTS (ACCOUNT_LIMIT, BALANCE) VALUES (80000, 0);
INSERT INTO CLIENTS (ACCOUNT_LIMIT, BALANCE) VALUES (1000000, 0);
INSERT INTO CLIENTS (ACCOUNT_LIMIT, BALANCE) VALUES (10000000, 0);
INSERT INTO CLIENTS (ACCOUNT_LIMIT, BALANCE) VALUES (500000, 0);

CREATE OR REPLACE FUNCTION CREATE_TRANSACTION(
	ID_CLIENT INT,
	T_VALUE INT,
	T_TYPE CHAR,
	T_DESC VARCHAR(10)
)
RETURNS TABLE (B INT, L INT) AS $$
	DECLARE
		CLIENT_BALANCE INTEGER;
		CLIENT_LIMIT INTEGER;
		
	BEGIN
		SELECT C.BALANCE, C.ACCOUNT_LIMIT 
		INTO CLIENT_BALANCE, CLIENT_LIMIT 
		FROM CLIENTS C
		WHERE ID = ID_CLIENT FOR UPDATE;

		IF NOT FOUND THEN
			RAISE EXCEPTION 'CLIENT_NOT_FOUND';
		END IF;

		IF T_TYPE = 'd' THEN 
			IF (CLIENT_BALANCE - T_VALUE) < (CLIENT_LIMIT * -1) THEN 
				RAISE EXCEPTION 'LOW_LIMIT';
			END IF;
			
			UPDATE CLIENTS 
			SET BALANCE = BALANCE - T_VALUE 
			WHERE ID = ID_CLIENT;
			
		ELSE
			UPDATE CLIENTS 
			SET BALANCE = BALANCE + T_VALUE 
			WHERE ID = ID_CLIENT;
		END IF;
		
		INSERT INTO TRANSACTIONS (TYPE, VALUE, DESCRIPTION, CLIENT_ID) 
		VALUES (T_TYPE, T_VALUE, T_DESC, ID_CLIENT);

		RETURN QUERY SELECT BALANCE, ACCOUNT_LIMIT  
		FROM CLIENTS WHERE ID = ID_CLIENT;
	END $$ 
	
LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION GET_BALANCE(CID INTEGER)
RETURNS TABLE (
	ACCOUNT_LIMIT INTEGER,
	BALANCE  INTEGER,
	VALUE  INTEGER,
	TYPE CHAR,
	DESCRIPTION VARCHAR(10),
	CREATED_AT TEXT
) AS $$ 
	BEGIN
		RETURN QUERY SELECT 
			C.ACCOUNT_LIMIT       
			, C.BALANCE           
			, T.VALUE             
			, T.TYPE::CHAR            
			, T.DESCRIPTION      
			, T.CREATED_AT::TEXT
		FROM CLIENTS C
		LEFT JOIN TRANSACTIONS T ON T.CLIENT_ID = C.ID
		WHERE C.ID = CID
		ORDER BY T.ID DESC
		LIMIT 10;
	END $$

LANGUAGE PLPGSQL;

COMMIT;