How to Version Control Oracle Queries in PostgreSQL

Why How to Version Control Oracle Queries in PostgreSQL is important

-- Team wants to audit changes to the monthly revenue query
INSERT INTO query_versions (query_name, version_no, git_commit, sql_text)
VALUES ('monthly_revenue', 2, 'd9e8f7g6', $$
    SELECT DATE_TRUNC('month', order_date) AS month,
           SUM(total_amount)               AS revenue
    FROM Orders
    GROUP BY month
    ORDER BY month;
$$);
-- Later, they pull the latest version for a dashboard
SELECT sql_text FROM query_versions
WHERE query_name = 'monthly_revenue'
ORDER BY version_no DESC LIMIT 1;

-- Create table to store query versions
CREATE TABLE query_versions (
    id            SERIAL PRIMARY KEY,
    query_name    TEXT NOT NULL,
    version_no    INTEGER NOT NULL,
    git_commit    CHAR(40),
    sql_text      TEXT NOT NULL,
    is_endorsed   BOOLEAN DEFAULT false,
    created_at    TIMESTAMP DEFAULT now()
);

-- Insert a new version of a customer report
INSERT INTO query_versions (query_name, version_no, git_commit, sql_text, is_endorsed)
VALUES ('customer_lifetime_value', 3, 'a1b2c3d4', $$
    SELECT c.id, c.name,
           SUM(oi.quantity * p.price) AS lifetime_value
    FROM Customers c
    JOIN Orders o  ON o.customer_id = c.id
    JOIN OrderItems oi ON oi.order_id = o.id
    JOIN Products p ON p.id = oi.product_id
    GROUP BY c.id, c.name;
$$, true);

-- Retrieve latest endorsed version
SELECT sql_text
FROM query_versions
WHERE query_name = 'customer_lifetime_value' AND is_endorsed = true
ORDER BY version_no DESC
LIMIT 1;