Toad Data Modeler is not able to parse CTE expressions in PostgreSQL (4379532)

Toad Data Modeler is not able to parse CTE expressions in PostgreSQL. It returns the error:

“ERROR: line 28, column 9, ending line 28, column 14: Found 'SELECT': Expecting: DELETE  INSERT  UPDATE”

Steps to reproduce the issue:

Run the script below in pgAdmin to create the tables and insert the example data:

-- 1. Create Tables
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    order_date DATE NOT NULL
);

CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    product_id INT REFERENCES products(product_id),
    quantity INT NOT NULL
);

-- 2. Insert Sample Data
INSERT INTO customers (name, email) VALUES
('Alice Smith', 'alice@example.com'),
('Bob Johnson', 'bob@example.com'),
('Carol Taylor', 'carol@example.com');

INSERT INTO products (name, price) VALUES
('Laptop', 1000.00),
('Mouse', 25.00),
('Keyboard', 45.00),
('Monitor', 150.00);

INSERT INTO orders (customer_id, order_date) VALUES
(1, '2024-01-01'),
(2, '2024-01-05'),
(1, '2024-02-10'),
(3, '2024-02-15');

INSERT INTO order_items (order_id, product_id, quantity) VALUES
(1, 1, 1),  -- Alice buys 1 Laptop
(1, 2, 2),  -- Alice buys 2 Mice
(2, 4, 1),  -- Bob buys 1 Monitor
(3, 3, 1),  -- Alice buys 1 Keyboard
(4, 2, 1);  -- Carol buys 1 Mouse

2. Create the function below that uses 2 CTE nested queries

CREATE OR REPLACE FUNCTION get_random_top_customer(min_spending NUMERIC)
RETURNS TABLE (
    out_customer_id INT,
    out_customer_name TEXT,
    out_total_spent NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    WITH outer_cte AS (
        WITH inner_cte AS (
            SELECT
                c.customer_id,
                c.name AS customer_name,
                SUM(p.price * oi.quantity) AS total_spent
            FROM customers c
            JOIN orders o ON c.customer_id = o.customer_id
            JOIN order_items oi ON o.order_id = oi.order_id
            JOIN products p ON oi.product_id = p.product_id
            GROUP BY c.customer_id, c.name
            HAVING SUM(p.price * oi.quantity) > min_spending
        )
        SELECT
            customer_id,
            customer_name,
            total_spent
        FROM inner_cte
    )
    SELECT
        customer_id,
        customer_name,
        total_spent
    FROM outer_cte
    ORDER BY random()
    LIMIT 1;
END;
$$ LANGUAGE plpgsql;

3. Run the query below to confirm that the function works and returns data:

SELECT * 
FROM get_random_top_customer(100);

4. Open Toad Data Modeler and create a Postgres model (File | New Model)
5. R-click on Function and select Add Function
6. Go to SQL tab , paste the function above and click Apply. TDM returns the error message “ERROR: line 28, column 9, ending line 28, column 14: Found 'SELECT': Expecting: DELETE  INSERT  UPDATE” but we know that the function is fine as tested before

bug TSA-262

WORKAROUND

None

STATUS 

Waiting for fix in a future release of Toad Data Modeler