TRUNCATE data_extract_as_is, editing, error_table, running_table, product_table, address_table, date_table, date_hierarchy, product_hierarchy,product_versioning_table;

SELECT * FROM product_table
SELECT * FROM product_hierarchy
SELECT * FROM public.product_versioning_table

CALL insert_product_hierarchy();

CREATE TABLE IF NOT EXISTS product_versioning_table 
(
    product_id VARCHAR(100) NOT NULL,  -- Product ID from product_hierarchy (duplicate allowed)
    product VARCHAR NOT NULL,          -- Product name
    price_each NUMERIC(10, 2) NOT NULL, -- Product price
    active BOOLEAN NOT NULL DEFAULT TRUE,  -- Automatically set to TRUE
    update_from DATE NOT NULL DEFAULT CURRENT_DATE, -- Automatically set to current date
    update_until DATE
);

CREATE OR REPLACE FUNCTION update_product_versioning_table()
RETURNS void AS $$
BEGIN
    -- Step 1: Mark old records as inactive (close the previous version by setting update_until date)
    UPDATE product_versioning_table
    SET update_until = CURRENT_DATE, active = FALSE
    WHERE product_id IN (
        SELECT ph.product_id
        FROM running_table rt
        JOIN product_hierarchy ph ON rt.product = ph.specific_product)
    AND active = TRUE;

    -- Step 2: Insert new active versions of products with the updated data
    INSERT INTO product_versioning_table (product_id, product, price_each, active, update_from)
    SELECT DISTINCT ph.product_id, rt.product, rt.price_each, TRUE, CURRENT_DATE
    FROM running_table rt
    JOIN product_hierarchy ph ON rt.product = ph.specific_product  -- Join on the 'product' field to get 'product_id'
    WHERE NOT EXISTS (
        SELECT 1
        FROM product_versioning_table pvt
        WHERE ph.product_id = pvt.product_id AND pvt.active = TRUE);

    RAISE NOTICE 'Product versioning table updated successfully.';
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION trigger_product_versioning_update()
RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE 'Trigger fired on running_table insert.';
    PERFORM update_product_versioning_table(); -- Call the updated function
    RETURN NULL; -- No return value for statement-level triggers
END;
$$ LANGUAGE plpgsql;


-- Drop existing trigger if exists
DROP TRIGGER IF EXISTS product_versioning_update_trigger ON running_table;
-- Create the trigger for running_table
CREATE TRIGGER product_versioning_update_trigger
AFTER INSERT ON running_table
FOR EACH STATEMENT
EXECUTE FUNCTION trigger_product_versioning_update();

-- First version of the product
INSERT INTO product_versioning_table (product_id, product, price_each)
VALUES('IPp-1', 'iPhone', 1999.99);

-- Second version of the same product with a different price
INSERT INTO product_versioning_table (product_id, product, price_each, active, update_from)
VALUES ('IPp-1', 'iPhone', 1099.99, TRUE, CURRENT_DATE);

I need my active to return false when there is a duplicate product_id