Stored procedure - PostgreSQL Query

Question

I'm new to stored procedure and want to achieve below ask using stored procedure in PostgreSQL.

I have requirement to calculate difference between 2 time period values like comparative analysis.

tbl_bv_temp_timeperiod

timperiod	p1	p2
FY 2022	Q1 2023	FY 2022
Q1 2023	H1 2023	Q1 2023
Q2 2023	Q2 2023	Q1 2023
H1 2023	H1 2023	FY 2022

This table records may increase in future.

I've written a SQL query and output as expected. However, I'm not able to store data into a table

    CREATE OR REPLACE PROCEDURE schema.sp_test_bv()
    LANGUAGE 'plpgsql'
    AS $$

    DECLARE

        p1 VARCHAR[]:= ARRAY(SELECT p1 FROM schema.tbl_bv_temp_timeperiod);
        p2 VARCHAR[]:= ARRAY(SELECT p2 FROM schema.tbl_bv_temp_timeperiod); 
        i INT:=1 record;
    
    BEGIN
        SET DATESTYLE TO 'SQL, MDY';
        FOR i IN SELECT COUNT(*) FROM schema.tbl_bv_temp_timeperiod 
            LOOP

    DROP TABLE IF EXISTS t1; 
    CREATE TEMPORARY TABLE t1 as
    (
      SELECT    client, client_sector, segmentation, rank_source, region, asset_class,
        products, currency_pair, product_type, period, period_new, year, bv_rank
      FROM  schema.tbl_bv_temp
      WHERE Period_new = SPLIT_PART(p1[i], ' ', 1) AND 
                year = SPLIT_PART(p1[i], ' ', 2)::int AND 
        bv_rank NOT LIKE ALL(ARRAY['Top%', '%-%', 'Tier%'])
      ORDER BY  client, region, products, product_type

     );

    DROP TABLE IF EXISTS t2;
    CREATE TEMPORARY TABLE t2 as
    (
    SELECT  client, client_sector, segmentation, rank_source, region, asset_class,
        products, currency_pair, product_type, period, period_new, year, bv_rank            
    FROM    schema.tbl_bv_temp
    WHERE   period_new = SPLIT_PART(p2[i], ' ', 1) AND 
                year = SPLIT_PART(p2[i], ' ', 2)::int AND 
        bv_rank NOT LIKE ALL(ARRAY['Top%', '%-%', 'Tier%'])
    ORDER BY    client, region, products, product_type
    );
    

    --Store below query output data into temp table or table and keep appending the 
    --table with output



    SELECT  x.client, x.client_sector, x.segmentation, x.rank_source, x.region, x.asset_class,
            x.products, x.currency_pair, x.product_type, CONCAT(SPLIT_PART(p1[i], ' ', 1), ' ', 
            RIGHT (SPLIT_PART(p1[i], ' ', 2), 2), ' vs. '  SPLIT_PART(p2[i], ' ', 1), ' ', 
                RIGHT(SPLIT_PART(p2[i], ' ', 2), 2)) AS period, 
            CASE    WHEN (x.bv_rank::int - y.bv_rank::int) > 0 THEN CONCAT((x.bv_rank::int - y.bv_rank::int)::VARCHAR, '  ')
              WHEN (x.bv_rank::int - y.bv_rank::int) < 0 THEN CONCAT((x.bv_rank::int - y.bv_rank::int)::VARCHAR, '  ')
          ELSE  CONCAT((x.bv_rank::int - y.bv_rank::int)::VARCHAR, '  ')
          END as bv_rank
    FROM        t1 as x
    LEFT JOIN   t2 as y
    ON  x.client = y.client AND
    x.client_sector = y.client_sector AND
    x.segmentation = y.segmentation AND
    x.rank_source = y.rank_source AND
    x.region = y.region AND
    x.asset_class = y.asset_class AND
    x.products = y.products AND
    x.currency_pair = y.currency_pair AND
    x.product_type = y.product_type 

    RAISE INFO '%', i;
    END LOOP;   
   END;
   $$

Please help with below challenge or guide me towards right direction