Скрипт очистки партиций

 
DECLARE
    schema_name              TEXT := 'top_product_storage';
    last_updated_datetime    TIMESTAMP;
    updated_interval         INTERVAL;
    partition_name           TEXT;
    found_partitions_to_drop BOOLEAN := false;
BEGIN
    DROP TABLE IF EXISTS tt_partitions;
    CREATE TEMP TABLE tt_partitions
        (
            name        TEXT,
            lower_bound BIGINT,
            upper_bound BIGINT
        );
 
    INSERT INTO tt_partitions
        SELECT name, lower_bound::BIGINT, upper_bound::BIGINT
        FROM partitioning.get_non_empty_table_partitions(schema_name, table_name);
 
    
    FOR partition_name IN (SELECT name FROM tt_partitions ORDER BY lower_bound DESC OFFSET keep_partitions_number)
    LOOP
        
        IF (NOT found_partitions_to_drop) THEN
            
            EXECUTE format('SELECT updated_at FROM %I.%I ORDER BY rev DESC LIMIT 1',
                schema_name, partition_name) INTO last_updated_datetime;
 
            IF (last_updated_datetime IS NOT NULL) THEN
                updated_interval := LOCALTIMESTAMP - last_updated_datetime;
                IF (updated_interval > old_data_interval) THEN
                    RAISE NOTICE 'Partition %.% with last updated date % is the latest partition to be dropped',
                        schema_name, partition_name, last_updated_datetime;
                    found_partitions_to_drop := true;
                END IF;
            END IF;
        END IF;
 
        
        IF (found_partitions_to_drop) THEN
            RAISE NOTICE 'Drop partition %.%', schema_name, partition_name;
            EXECUTE format('DROP TABLE %I.%I', schema_name, partition_name);
        END IF;
    END LOOP;
END