gistfile1.sql

DROP TABLE IF EXISTS "user" CASCADE;
DROP TABLE IF EXISTS match CASCADE;
DROP TABLE IF EXISTS transfer CASCADE;
DROP TABLE IF EXISTS currency CASCADE;
DROP TABLE IF EXISTS account CASCADE;
DROP TABLE IF EXISTS "order" CASCADE;
DROP DOMAIN IF EXISTS account_volume CASCADE;
DROP DOMAIN IF EXISTS order_price CASCADE;
DROP DOMAIN IF EXISTS order_volume CASCADE;

CREATE DOMAIN account_volume AS numeric(18, 8);
CREATE DOMAIN order_price AS numeric(15, 5);
CREATE DOMAIN order_volume AS numeric(13, 3);

CREATE TABLE currency (
    currency_id char(3) PRIMARY KEY
);

CREATE TABLE "user" (
	user_id SERIAL PRIMARY KEY,
	username varchar(20) NOT NULL,
	username_lower varchar(20) NOT NULL UNIQUE,
	password varchar(60) NOT NULL
);

CREATE OR REPLACE FUNCTION user_update_username_lower() RETURNS trigger AS $$
BEGIN
	NEW.username_lower := lower(NEW.username);
	RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER user_insert
	BEFORE INSERT OR UPDATE ON "user"
	FOR EACH ROW
	EXECUTE PROCEDURE user_update_username_lower();

CREATE TABLE account (
    account_id SERIAL PRIMARY KEY,
    currency_id char(3) NOT NULL REFERENCES currency(currency_id),
    user_id int REFERENCES "user"(user_id),
    balance account_volume NOT NULL DEFAULT 0,
    created timestamp NOT NULL DEFAULT now(),
    special varchar(100)
    CHECK (user_id IS NULL OR special IS NULL),
    CHECK (special IS NOT NULL OR balance >= 0)
);

CREATE TABLE transfer (
    transfer_id SERIAL PRIMARY KEY,
    from_account_id int NOT NULL REFERENCES account(account_id),
    to_account_id int NOT NULL REFERENCES account(account_id),
    volume account_volume NOT NULL CHECK (volume > 0),
    CHECK(from_account_id <> to_account_id)
);

CREATE TABLE "order" (
    order_id SERIAL PRIMARY KEY,
    user_id int NOT NULL REFERENCES "user"(user_id),
    have char(3) NOT NULL REFERENCES currency(currency_id),
    want char(3) NOT NULL REFERENCES currency(currency_id),
    price order_price NOT NULL CHECK (price > 0),
    volume order_volume NOT NULL CHECK (volume >= 0),
    CHECK (have <> want)
);

CREATE OR REPLACE FUNCTION account_for_user_currency (
    uid int,
    currency char(3)
) RETURNS int AS $$
DECLARE
	res int;
BEGIN
	SELECT account_id INTO res
	FROM account
	WHERE user_id = uid;

	IF NOT FOUND THEN
		INSERT INTO account (user_id, currency_id) VALUES (uid, currency);
		res := currval('account_account_id_seq');
	END IF;

	RETURN res;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION special_account (
    name varchar(100),
    currency char(3)
) RETURNS int AS $$
DECLARE
	res int;
BEGIN
	SELECT account_id INTO res
	FROM account a
	WHERE special = name AND currency = a.currency_id;

	IF NOT FOUND THEN
		INSERT INTO account (special, currency_id) VALUES (name, currency);
		res := currval('account_account_id_seq');
	END IF;

	RETURN res;
END; $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION order_transfer() RETURNS trigger AS $$
BEGIN
    INSERT INTO transfer (from_account_id, to_account_id, volume)
    VALUES (
        account_for_user_currency(NEW.user_id, NEW.have),
        special_account('orders', NEW.have),
        NEW.volume
    );
    RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER order_insert_transfer
    BEFORE INSERT ON "order"
    FOR EACH ROW
    EXECUTE PROCEDURE order_transfer();

CREATE OR REPLACE FUNCTION transfer_update_balances() RETURNS trigger AS $$
BEGIN
	UPDATE account SET balance = balance - NEW.volume
	WHERE account_id = NEW.from_account_id;

	UPDATE account SET balance = balance + NEW.volume
	WHERE account_id = NEW.to_account_id;

	RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER transfer_insert
	BEFORE INSERT ON transfer
	FOR EACH ROW
	EXECUTE PROCEDURE transfer_update_balances();

CREATE TABLE match (
	match_id SERIAL PRIMARY KEY,
	created timestamp NOT NULL DEFAULT now(),
	left_order_id int NOT NULL REFERENCES "order"(order_id),
	right_order_id int NOT NULL REFERENCES "order"(order_id),
	left_volume order_volume NOT NULL CHECK (left_volume > 0),
	right_volume order_volume NOT NULL CHECK (right_volume > 0)
);

CREATE OR REPLACE FUNCTION match_order () RETURNS trigger AS $$
DECLARE
	lefto "order"%ROWTYPE;
	righto "order"%ROWTYPE;
	leftprice order_price;
	leftvol order_volume;
	rightvol order_volume;
BEGIN
	IF NEW.volume = 0 THEN
		RAISE EXCEPTION 'order inserted with zero volume';
	END IF;

	RAISE NOTICE 'matching order %', NEW.order_id;

	SELECT * INTO lefto FROM "order" o WHERE o.order_id = NEW.order_id;

	SELECT * INTO righto FROM "order" o		
	WHERE 
		o.have = lefto.want AND
		o.want = lefto.have AND
		o.volume > 0
	ORDER BY
		o.price ASC, o.order_id ASC;

	IF NOT FOUND THEN
		RAISE NOTICE 'nothing to match order % with', NEW.order_id;
		RETURN NEW;
	END IF;

	RAISE NOTICE 'left order % will be matched with right order %', NEW.order_id, righto.order_id;

	leftprice := ceil(1 / righto.price * 10^5) / 10^5;

	IF leftprice * lefto.volume <= righto.volume THEN
		RAISE NOTICE 'left order will be fulfilled';
		leftvol = lefto.volume;
		rightvol = lefto.volume * leftprice;
	ELSE
		RAISE NOTICE 'right order will be fulfilled';
		rightvol = righto.volume;
		leftvol = righto.volume * righto.price;
	END IF;

	RAISE NOTICE 'inserting match. left vol %, right vol %', leftvol, rightvol;

	INSERT INTO "match" (left_order_id, right_order_id, left_volume, right_volume)
	VALUES (lefto.order_id, righto.order_id, leftvol, rightvol);

	IF leftvol < lefto.volume THEN
		SELECT match_order(NEW.order_id);
	END IF;
	
	RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_orders_and_transfer_from_match() RETURNS trigger AS $$
DECLARE
	lefto "order"%ROWTYPE;
	righto "order"%ROWTYPE;
BEGIN
	UPDATE "order"
	SET volume = volume - NEW.left_volume
	WHERE order_id = NEW.left_order_id;

	UPDATE "order"
	SET volume = volume - NEW.right_volume
	WHERE order_id = NEW.right_order_id;

	SELECT * INTO lefto FROM "order" WHERE order_id = NEW.left_order_id;
	SELECT * INTO righto FROM "order" WHERE order_id = NEW.right_order_id;
	
	INSERT INTO transfer (from_account_id, to_account_id, volume)
	VALUES 
		(
			special_account('orders', lefto.want), 
			account_for_user_currency(lefto.user_id, lefto.want),
			NEW.right_volume
		),
		(
			special_account('orders', righto.want), 
			account_for_user_currency(righto.user_id, righto.want),
			NEW.left_volume
		);

	RETURN NEW;		
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER match_inserted
	AFTER INSERT ON "match"
	FOR EACH ROW
	EXECUTE PROCEDURE update_orders_and_transfer_from_match();

CREATE TRIGGER order_insert_match
	AFTER INSERT ON "order"
	FOR EACH ROW
	EXECUTE PROCEDURE match_order();

INSERT INTO currency (currency_id) VALUES ('BTC');
INSERT INTO currency (currency_id) VALUES ('USD');
INSERT INTO "user" (username, password) VALUES ('abrkn', 'pass');
INSERT INTO "user" (username, password) VALUES ('simen', 'pass');
SELECT * FROM "user";

INSERT INTO transfer (from_account_id, to_account_id, volume)
	VALUES (special_account('faucet', 'BTC'), account_for_user_currency(1, 'BTC'), 5);
	
INSERT INTO transfer (from_account_id, to_account_id, volume)
	VALUES (special_account('faucet', 'USD'), account_for_user_currency(2, 'USD'), 25);
	
INSERT INTO "order" (user_id, have, want, price, volume)
	VALUES (2, 'USD', 'BTC', 0.08333, 5.25);
	
INSERT INTO "order" (user_id, have, want, price, volume)
	VALUES (2, 'USD', 'BTC', 0.081, 1);
		
INSERT INTO "order" (user_id, have, want, price, volume)
	VALUES (1, 'BTC', 'USD', 11, 1.5);

output:

NOTICE:  matching order 1
NOTICE:  nothing to match order 1 with
NOTICE:  matching order 2
NOTICE:  nothing to match order 2 with
NOTICE:  matching order 3
NOTICE:  left order 3 will be matched with right order 2
NOTICE:  right order will be fulfilled
ERROR:  cache lookup failed for type 28505
CONTEXT:  SQL statement "INSERT INTO match (left_order_id, right_order_id, left_volume, right_volume)
    VALUES (lefto.order_id, righto.order_id, leftvol, rightvol)"
PL/pgSQL function match_order(integer) line 33 at SQL statement
SQL statement "SELECT match_order(NEW.order_id)"
PL/pgSQL function match_order() line 50 at SQL statement

********** Error **********

ERROR: cache lookup failed for type 28505
SQL state: XX000
Context: SQL statement "INSERT INTO match (left_order_id, right_order_id, left_volume, right_volume)
    VALUES (lefto.order_id, righto.order_id, leftvol, rightvol)"
PL/pgSQL function match_order(integer) line 33 at SQL statement
SQL statement "SELECT match_order(NEW.order_id)"
PL/pgSQL function match_order() line 50 at SQL statement