DELETES not able to return # rows deleted -- tried changes() and RETURNING COUNT(*) etc

[msiddiqui24]

SQLDelight Version

app.cash.sqldelight:postgresql-dialect:2.0.2

Operating System

Mac OS 14.4.1 (23E224)

Gradle Version

8.6

Kotlin Version

1.9.22

Dialect

app.cash.sqldelight.dialects.postgresql.PostgreSqlDialect

AGP Version

No response

Describe the Bug

I am trying to create a delete query that returns # rows affected (saw an example here)

however, i get the below error:

/Users/msiddiqui/ai-3p-connector/permission-service/persistence/src/main/sqldelight/membership/data/Groups.sq: (125, 9): Unknown function changes
125    SELECT changes()
                ^^^^^^^^^
FAILURE: Build failed with an exception.
* What went wrong:

I also tried an aggregate RETURNING, but it did not allow aggregate functions as the return.

Stacktrace

No response

Gradle Build Script

No response

[griffio]

🗞️ This is related to issue https://github.com/cashapp/sqldelight/pull/4578 Where the rows affected is not exposed(intentionally) from the driver (jdbc) implementation - so only Unit is returned.

The changes function is built into Sqlite, so won't work for other databases

As you mentioned, Postgresql can't use Returning with an aggregate function e.g RETURNING COUNT(*)

For SqlDelight, you can use a CTE like this for your query

delete:
WITH deleted_rows AS (
    DELETE FROM MyTable RETURNING *
)
SELECT COUNT(*) FROM deleted_rows;

Generates a query that returns the rows deleted

    val count = sample.testQueries.delete().executeAsOne()

[msiddiqui24]

this did not work for me, it does not successfully delete

[griffio]

🤔 I am not sure why that doesn't work

However, I can only provide a fully working Postgresql example https://github.com/griffio/sqldelight-postgres-example

e.g https://github.com/griffio/sqldelight-postgres-example/blob/master/src/main/sqldelight/griffio/queries/City.sq

See if that helps to solve issue