CREATE OR REPLACE FUNCTION process_large_data()
RETURNS VOID AS $$
DECLARE
data_record camera_collection_bak_20240515%ROWTYPE;
cursor_name CURSOR FOR SELECT * FROM camera_collection_bak_20240515 ;
BEGIN
OPEN cursor_name;
LOOP
FETCH cursor_name INTO data_record;
EXIT WHEN NOT FOUND;
-- 插入到目标表
insert into camera_collection ( index_code,
external_index_code,
region_index_code,
name,
region_code,
region_path,
deleted )
VALUES (data_record.index_code,
data_record.external_index_code
,data_record.region_index_code,
data_record.name,
data_record.region_code,
data_record.region_path,
data_record.deleted) ON conflict(index_code) DO UPDATE SET external_index_code = EXCLUDED.external_index_code,
region_index_code = EXCLUDED.region_index_code,
name = EXCLUDED.name,
region_code = EXCLUDED.region_code,
region_path = EXCLUDED.region_path,
deleted = EXCLUDED.deleted,
update_time = now();
END LOOP;
CLOSE cursor_name;
END;
$$ LANGUAGE plpgsql;
call process_large_data();
