Create schema ch_spp_shps3_skif;
CREATE TABLE ch_spp_shps3_skif.opc_2tags_pb (
id SERIAL4 PRIMARY KEY,
datakey VARCHAR(255) NOT NULL,
datavalue VARCHAR(255),
datats INT8
);
insert into ch_spp_shps3_skif.opc_2tags_pb(datakey, datavalue, datats)
values('1', 'v1', 1749451566584),
('2', 'v2', 1749451566584),
('3', 'v3', 1749451566584);
CREATE TABLE ch_spp_shps3_skif.v_opc_2tags_pb (
id serial4 PRIMARY KEY,
opc_2tags_pb_id int8 REFERENCES ch_spp_shps3_skif.opc_2tags_pb(id),
datakey varchar(255),
value varchar(255),
date timestamptz
);
CREATE PROCEDURE ch_spp_shps3_skif.refresh_v_opc_2tags_pb()
LANGUAGE plpgsql
AS $$
DECLARE
now_date date := now()::date + interval '1 day';
start_date timestamp := now_date - interval '1 day';
end_date timestamp := now_date - interval '1 second';
BEGIN
WITH filtered AS (
SELECT *
FROM ch_spp_shps3_skif.opc_2tags_pb
WHERE to_timestamp(datats / 1000)
BETWEEN start_date AND end_date
),
ranked AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY datakey
ORDER BY datats DESC
) AS rn
FROM filtered
)
INSERT INTO ch_spp_shps3_skif.v_opc_2tags_pb (opc_2tags_pb_id, datakey, value, date)
SELECT id, datakey, datavalue, to_timestamp(datats / 1000):: timestamptz as date
FROM ranked
WHERE rn = 1;
END;
$$;
call ch_spp_shps3_skif.refresh_v_opc_2tags_pb();
CREATE OR REPLACE PROCEDURE ch_spp_shps3_skif.refresh_v_opc_2tags_pb2()
LANGUAGE plpgsql
AS $$
DECLARE
now_date date := now()::date + interval '2 day';
start_date timestamp := now_date - interval '1 day';
end_date timestamp := now_date - interval '1 second';
BEGIN
WITH filtered AS (
SELECT *
FROM ch_spp_shps3_skif.opc_2tags_pb
WHERE to_timestamp(datats / 1000)
BETWEEN start_date AND end_date
),
ranked AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY datakey
ORDER BY datats DESC
) AS rn
FROM filtered
)
INSERT INTO ch_spp_shps3_skif.v_opc_2tags_pb (opc_2tags_pb_id, datakey, value, date)
SELECT id, datakey, datavalue, to_timestamp(datats / 1000):: timestamptz as date
FROM ranked
WHERE rn = 1;
END;
$$;
insert into ch_spp_shps3_skif.opc_2tags_pb(datakey, datavalue, datats) values ('1', 'v1_2', 1749551566584),
('2', 'v2_2', 1749551566584),
('2', 'v2_22', 1749558566584),
('2', 'v2_21', 1749581566584),
('3', 'v3_2', 1749551566584);
call ch_spp_shps3_skif.refresh_v_opc_2tags_pb2();
insert into ch_spp_shps3_skif.opc_2tags_pb(datakey, datavalue, datats) values ('1', 'v1_3', 1749671566584);
CREATE OR REPLACE PROCEDURE ch_spp_shps3_skif.refresh_v_opc_2tags_pb3()
LANGUAGE plpgsql
AS $$
DECLARE
now_date date := now()::date + interval '3 day';
start_date timestamp := now_date - interval '1 day';
end_date timestamp := now_date - interval '1 second';
BEGIN
WITH filtered AS (
SELECT *
FROM ch_spp_shps3_skif.opc_2tags_pb
WHERE to_timestamp(datats / 1000)
BETWEEN start_date AND end_date
),
ranked AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY datakey
ORDER BY datats DESC
) AS rn
FROM filtered
)
INSERT INTO ch_spp_shps3_skif.v_opc_2tags_pb (opc_2tags_pb_id, datakey, value, date)
SELECT id, datakey, datavalue, to_timestamp(datats / 1000):: timestamptz as date
FROM ranked
WHERE rn = 1;
END;
$$;
call ch_spp_shps3_skif.refresh_v_opc_2tags_pb3();
select * from ch_spp_shps3_skif.v_opc_2tags_pb
Telegram: DB developers channel