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 SEQUENCE id START 1; CREATE FUNCTION auction_f() RETURNS TRIGGER AS $$ DECLARE auctioneer_ TEXT; attachment_ TEXT; bet_ TEXT; id_ INTEGER; BEGIN select nextval('id') into id_; auctioneer_ := new.payload::json->'auctioneer'; attachment_ := new.payload::json->'attachment'; bet_ := new.payload::json->'bet'; insert into auctioneer values( id_, auctioneer_::json->>'firstname', auctioneer_::json->>'lastname', auctioneer_::json->>'nickname', auctioneer_::json->>'email' ); insert into attachment values( id_, attachment_::json->>'filename', attachment_::json#>>'{location,datacenter}', attachment_::json#>>'{location,localname}' ); insert into bet values( id_, CAST(bet_::json->>'volume' AS NUMERIC), '1970-01-01 00:00:00+00'::TIMESTAMP + CAST(bet_::json->>'ts' AS INTEGER) * interval'1 second' --epoch-- + CAST(bet_::json->>'ts' AS TIMESTAMP) --DATEFROMPARTS(bet_::json->>'ts') --DATEADD(s, CAST(bet_::json->>'ts' as INT), '1970-01-01 00:00:00') ); return new; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE TRIGGER auction_vw_trg INSTEAD OF INSERT ON v_auction_payload FOR EACH ROW EXECUTE FUNCTION auction_f(); ---------------------------------------------------------------------------------- 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; SELECT * FROM attachment; SELECT * FROM bet; SELECT * FROM v_auction_payload;