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;
Telegram: DB developers channel