SQLize Online / PHPize Online  /  SQLtest Online

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