SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE auctioneer ( event_id INTEGER, firstname TEXT, lastname TEXT, nickname TEXT, email TEXT ); CREATE TABLE attachment ( event_id INTEGER, filename TEXT, datacenter TEXT, localname TEXT ); CREATE TABLE bet ( event_id INTEGER, volume NUMERIC, ts TIMESTAMP ); CREATE OR REPLACE FUNCTION auctioneer_to_json(event_id INTEGER) RETURNS TEXT LANGUAGE plpgsql AS $$ DECLARE firstname_ TEXT; lastname_ TEXT; nickname_ TEXT; email_ TEXT; BEGIN SELECT firstname, lastname, nickname, email INTO firstname_, lastname_, nickname_, email_ FROM auctioneer WHERE auctioneer.event_id = $1; RETURN format( '{"firstname": %I, "lastname": %I, "nickname": %I, "email": %I}', firstname_, lastname_, nickname_, email_ ); END; $$; CREATE OR REPLACE FUNCTION attachment_to_json(event_id INTEGER) RETURNS TEXT LANGUAGE plpgsql AS $$ DECLARE filename_ TEXT; datacenter_ TEXT; localname_ TEXT; BEGIN SELECT filename, datacenter, localname INTO filename_, datacenter_, localname_ FROM attachment WHERE attachment.event_id = $1; RETURN format( '{"filename": %I, location": {"datacenter": %I, "localname": %I}}', filename_, datacenter_, localname_ ); END; $$; CREATE OR REPLACE FUNCTION bet_to_json(event_id INTEGER) RETURNS TEXT LANGUAGE plpgsql AS $$ DECLARE volume_ NUMERIC; ts_ TIMESTAMP; BEGIN SELECT volume, ts INTO volume_, ts_ FROM bet WHERE bet.event_id = $1; RETURN format( '{"volume": %s, "ts": %s}', volume_, EXTRACT(EPOCH FROM ts_)::BIGINT ); END; $$; CREATE OR REPLACE VIEW v_auction_payload(payload) AS SELECT format( '{"auctioneer": %s, "attachment": %s, "bet": %s}', auctioneer_to_json(bet.event_id), attachment_to_json(bet.event_id), bet_to_json(bet.event_id) ) FROM auctioneer JOIN attachment ON auctioneer.event_id = attachment.event_id JOIN bet ON attachment.event_id = bet.event_id; CREATE OR REPLACE VIEW checker AS SELECT *; CREATE OR REPLACE TRIGGER reminder1 BEFORE INSERT ON v_auction_payload EXECUTE checker; INSERT INTO v_auction_payload(payload) VALUES ( '{ "auctioneer": { "firstname": "john", "lastname": "doe", "nickname": "mr.notknown", "email": "john.doe@email.com" }, "attachment": { "filename": "key.token1", "location": { "datacenter": "AWS_EU", "localname": "iD1234sdv23r23rtfwv" } }, "bet": { "volume": 0.00001, "ts": 1672520400 } }'); /*INSERT INTO v_auction_payload(payload) VALUES ( '{ "auctioneer": { "firstname": "john", "lastname": "doe", "nickname": "mr.notknown", "email": "john.doe@email.com" }, "attachment": { "filename": "key.token2", "location": { "datacenter": "AWS_US", "localname": "iDasd@-asfasf23@3s" } }, "bet": { "volume": 0.00085, "ts": 1676928960 } }'); SELECT * FROM auctioneer;*/