CREATE OR REPLACE PROCEDURE public.update_transaction_status()
LANGUAGE plpgsql
AS $$
DECLARE transaction RECORD;
BEGIN
RAISE NOTICE 'updating transaction.status';
FOR transaction IN
SELECT * FROM public."Transaction"
LOOP
IF CURRENT_DATE >= transaction."endDate" AND transaction.status = 'ACTIVE' THEN
UPDATE public."Transaction"
SET status = 'COMPLETED'
WHERE id = transaction.id;
RAISE NOTICE 'transaction % updated to completed!', transaction.id;
END IF;
IF CURRENT_DATE <= transaction."endDate" AND transaction.status = 'OPEN' THEN
UPDATE public."Transactioni"
SET status = 'ACTIVE'
WHERE id = transaction.id;
RAISE NOTICE 'transaction % updated to active!', transaction.id;
END IF;
END LOOP;
END
$$;