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 table if not exists a(str text); create or replace function v_new_insert() returns trigger as $$ begin insert into a select NEW.*; return NEW; end; $$ language plpgsql; create trigger log_update instead of insert ON v_auction_payload for each row EXECUTE FUNCTION v_new_insert(); 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 } }'); select * from a;