SQL Fragment Functions

SQL fragment functions allow you to use Kotlin functions inside of sql blocks. Writing a SQL fragment function is as simple as adding the @SqlFragment annotation to a function that returns a SqlQuery<T> or SqlExpression<T> instance. Recall that in the introduction we saw a SQL fragment function that calculated the P/E ratio of a stock:

  @SqlFragment
fun peRatioWeighted(stock: Stock, weight: Double): Double = sql.expression {
    (stock.price / stock.earnings) * weight
  }

Once this function is defined you can use it inside an sql block like this:

sql {
  Table<Stock>().map { stock -> peRatioWeighted(stock, stock.marketCap / totalWeight) }
}

Note that SQL fragment functions can call other SQL fragment functions, for example:

@SqlFragment
fun peRationSimple(stock: Stock): Double = sql.expression {
    stock.price / stock.earnings
  }

@SqlFragment
fun peRatioWeighted(stock: Stock, weight: Double): Double = sql.expression {
  peRationSimple(stock) * weight
}
sql {
  Table<Stock>().map { stock -> peRatioWeighted(stock, stock.marketCap / totalWeight) }
}

Also note that SQL fragment functions can make use of the context-receiver position. For example, let’s make the marketCap field into a function:

@SqlFragment
fun Stock.marketCap() = sql.expression {
    price * sharesOutstanding
  }
val q = sql {
  val totalWeight = Table<Stock>().map { it.marketCap().use }.sum() // A local variable used in the query!
  Table<Stock>().map { stock -> peRatioWeighted(stock, stock.marketCap().use / totalWeight) }
}
println(q.buildFor.Postgres().value)
// SELECT (stock.price / stock.earnings) * ((this.price * this.sharesOutstanding) / (SELECT sum(this.price * this.sharesOutstanding) FROM Stock it)) AS value FROM Stock stock

Since Sql Fragment Functions guarantee that the code inside of them leads to a compile-time generated query they cannot be used arbitrarily. They can only contain a single capture, capture.select, or capture.expression block. They cannot have any other kind of control logic (e.g. if, when, for, etc.) inside of them. If you want a more flexible mechanism for writing queries see the Dynamic Queries section.