How to Run Complex Multi-Statement SQL Reports Efficiently in Ecto & Ash Without Leaving Performance on the Table

Kamaro Lambert

Follow

3 min read

·

Apr 12, 2026

Listen

Share

Press enter or click to view image in full size

Sometimes it makes perfect sense to run a query directly on the database. Certain logic, especially complex reporting performs better and faster when handled entirely in SQL rather than in application code.

These queries often involve multiple statements: creating a temporary table, inserting data from various sources, enriching it with joins, performing calculations (like running balances), and finally returning the result. This pattern is common when generating reports.

A typical flow looks like this in your SQL editor (e.g., pgAdmin):

-- 1. Create report structure table
DROP TABLE IF EXISTS temp_loan_history;
CREATE TEMPORARY TABLE temp_loan_history(...)

-- 2. Insert data in the report structure
INSERT INTO temp_loan_history(...) SELECT * FROM loans;

-- 3. Insert loan repayments
INSERT INTO temp_loan_history(...) SELECT * FROM loan_repayments;

-- 4. Calculate running balance etc...

The Problem with Ecto

If you try to run multiple statements with MyApp.Repo.query/2, Ecto (via Postgrex) does not allow it in a single call.

If you split them into separate Repo.query/2 calls outside a transaction, you’ll hit this…