Return custom types by SUM, COALESCE

[Knoxvillekm]

Description

CREATE TABLE SomeTable (
    ...
    field TEXT AS CustomType NOT NULL 
);

selectSum:
SELECT SUM(field) FROM SomeTable;

selectSum:
SELECT COALESCE(field, CustomType(defaultValue)) FROM SomeTable;

For now SUM return Double value and cannot set default value to COALESCE.
Idea:
Add some override fun to Adapder

ColumnAdapter<CustomType, String> {
  override fun decode(databaseValue: String)
  override fun encode(value: CustomType)
  override fun sum(value: CustomType, value2: CustomType)
  override fun coalesce(value: CustomType, defaultValue: CustomType)
}

[hfhbd]

I don't think we want to introduce custom sql handling. If you have a valid SQL syntax calculating the sum of text, I am open. Same for coalescing.

[Knoxvillekm]

How can I return CustomType from SUM and COALESCE?

selectSum:
SELECT SUM(field) AS CustomType FROM SomeTable;

selectSum:
SELECT COALESCE(field, "") AS CustomType FROM SomeTable;

AS CustomType - just rename returned field.

[hfhbd]

We really try to avoid changing the sql dialect because this make many thinks way harder, sql itself is often ambiguous. There is a similar issue and maybe we will add it somehow: #3421 (comment)

If you really want this functionallity in sql and not in Kotlin, there is a workound through, but requires a little overhead: you must create your own dialect to override this function: https://github.com/cashapp/sqldelight/tree/master/sqldelight-gradle-plugin/src/test/custom-dialect