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