ktorm-support-postgresql/org.ktorm.support.postgresql/insertOrUpdateReturning

insertOrUpdateReturning

fun <T : BaseTable<*>, C : Any> Database.insertOrUpdateReturning(table: T, returning: Column<C>, block: InsertOrUpdateStatementBuilder.(T) -> Unit): C?(source)

Insert a record to the table, determining if there is a key conflict while it's being inserted, automatically performs an update if any conflict exists, and finally returns the specific column.

Usage:

val id = database.insertOrUpdateReturning(Employees, Employees.id) {
    set(it.id, 1)
    set(it.name, "vince")
    set(it.job, "engineer")
    set(it.salary, 1000)
    set(it.hireDate, LocalDate.now())
    set(it.departmentId, 1)
    onConflict {
        set(it.salary, it.salary + 900)
    }
}

Generated SQL:

insert into t_employee (id, name, job, salary, hire_date, department_id)
values (?, ?, ?, ?, ?, ?)
on conflict (id) do update set salary = t_employee.salary + ?
returning id

By default, the column used in the on conflict statement is the primary key you already defined in the schema definition. If you want, you can specify one or more columns for the on conflict statement as belows:

val id = database.insertOrUpdateReturning(Employees, Employees.id) {
    set(it.id, 1)
    set(it.name, "vince")
    set(it.job, "engineer")
    set(it.salary, 1000)
    set(it.hireDate, LocalDate.now())
    set(it.departmentId, 1)
    onConflict(it.name, it.job) {
        set(it.salary, it.salary + 900)
    }
}

Generated SQL:

insert into t_employee (id, name, job, salary, hire_date, department_id)
values (?, ?, ?, ?, ?, ?)
on conflict (name, job) do update set salary = t_employee.salary + ?
returning id

Since

3.4.0

Return

the returning column's value.

Parameters

table

the table to be inserted.

returning

the column to return

block

the DSL block used to construct the expression.

fun <T : BaseTable<*>, C1 : Any, C2 : Any> Database.insertOrUpdateReturning(table: T, returning: Pair<Column<C1>, Column<C2>>, block: InsertOrUpdateStatementBuilder.(T) -> Unit): Pair<C1?, C2?>(source)

Insert a record to the table, determining if there is a key conflict while it's being inserted, automatically performs an update if any conflict exists, and finally returns the specific columns.

Usage:

val (id, job) = database.insertOrUpdateReturning(Employees, Pair(Employees.id, Employees.job)) {
    set(it.id, 1)
    set(it.name, "vince")
    set(it.job, "engineer")
    set(it.salary, 1000)
    set(it.hireDate, LocalDate.now())
    set(it.departmentId, 1)
    onConflict {
        set(it.salary, it.salary + 900)
    }
}

Generated SQL:

insert into t_employee (id, name, job, salary, hire_date, department_id)
values (?, ?, ?, ?, ?, ?)
on conflict (id) do update set salary = t_employee.salary + ?
returning id, job

val (id, job) = database.insertOrUpdateReturning(Employees, Pair(Employees.id, Employees.job)) {
    set(it.id, 1)
    set(it.name, "vince")
    set(it.job, "engineer")
    set(it.salary, 1000)
    set(it.hireDate, LocalDate.now())
    set(it.departmentId, 1)
    onConflict(it.name, it.job) {
        set(it.salary, it.salary + 900)
    }
}

Generated SQL:

insert into t_employee (id, name, job, salary, hire_date, department_id)
values (?, ?, ?, ?, ?, ?)
on conflict (name, job) do update set salary = t_employee.salary + ?
returning id, job

Since

3.4.0

Return

the returning columns' values.

Parameters

table

the table to be inserted.

returning

the columns to return

block

the DSL block used to construct the expression.

fun <T : BaseTable<*>, C1 : Any, C2 : Any, C3 : Any> Database.insertOrUpdateReturning(table: T, returning: Triple<Column<C1>, Column<C2>, Column<C3>>, block: InsertOrUpdateStatementBuilder.(T) -> Unit): Triple<C1?, C2?, C3?>(source)

Insert a record to the table, determining if there is a key conflict while it's being inserted, automatically performs an update if any conflict exists, and finally returns the specific columns.

Usage:

val (id, job, salary) =
database.insertOrUpdateReturning(Employees, Triple(Employees.id, Employees.job, Employees.salary)) {
    set(it.id, 1)
    set(it.name, "vince")
    set(it.job, "engineer")
    set(it.salary, 1000)
    set(it.hireDate, LocalDate.now())
    set(it.departmentId, 1)
    onConflict {
        set(it.salary, it.salary + 900)
    }
}

Generated SQL:

insert into t_employee (id, name, job, salary, hire_date, department_id)
values (?, ?, ?, ?, ?, ?)
on conflict (id) do update set salary = t_employee.salary + ?
returning id, job, salary

val (id, job, salary) =
database.insertOrUpdateReturning(Employees, Triple(Employees.id, Employees.job, Employees.salary)) {
    set(it.id, 1)
    set(it.name, "vince")
    set(it.job, "engineer")
    set(it.salary, 1000)
    set(it.hireDate, LocalDate.now())
    set(it.departmentId, 1)
    onConflict(it.name, it.job) {
        set(it.salary, it.salary + 900)
    }
}

Generated SQL:

insert into t_employee (id, name, job, salary, hire_date, department_id)
values (?, ?, ?, ?, ?, ?)
on conflict (name, job) do update set salary = t_employee.salary + ?
returning id, job, salary

Since

3.4.0

Return

the returning columns' values.

Parameters

table

the table to be inserted.

returning

the columns to return

block

the DSL block used to construct the expression.