ktorm-support-postgresql/org.ktorm.support.postgresql/bulkInsertOrUpdateReturning

bulkInsertOrUpdateReturning

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

Bulk insert records to the table, determining if there is a key conflict while inserting each of them, automatically performs updates if any conflict exists, and finally returns the specific column.

Usage:

database.bulkInsertOrUpdateReturning(Employees, Employees.id) {
    item {
        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)
    }
    item {
        set(it.id, 5)
        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:

database.bulkInsertOrUpdateReturning(Employees, Employees.id) {
    item {
        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)
    }
    item {
        set(it.id, 5)
        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 values.

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.bulkInsertOrUpdateReturning(table: T, returning: Pair<Column<C1>, Column<C2>>, block: BulkInsertOrUpdateStatementBuilder<T>.(T) -> Unit): List<Pair<C1?, C2?>>(source)

Bulk insert records to the table, determining if there is a key conflict while inserting each of them, automatically performs updates if any conflict exists, and finally returns the specific columns.

Usage:

database.bulkInsertOrUpdateReturning(Employees, Pair(Employees.id, Employees.job)) {
    item {
        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)
    }
    item {
        set(it.id, 5)
        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

database.bulkInsertOrUpdateReturning(Employees, Pair(Employees.id, Employees.job)) {
    item {
        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)
    }
    item {
        set(it.id, 5)
        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.bulkInsertOrUpdateReturning(table: T, returning: Triple<Column<C1>, Column<C2>, Column<C3>>, block: BulkInsertOrUpdateStatementBuilder<T>.(T) -> Unit): List<Triple<C1?, C2?, C3?>>(source)

Bulk insert records to the table, determining if there is a key conflict while inserting each of them, automatically performs updates if any conflict exists, and finally returns the specific columns.

Usage:

database.bulkInsertOrUpdateReturning(Employees, Triple(Employees.id, Employees.job, Employees.salary)) {
    item {
        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)
    }
    item {
        set(it.id, 5)
        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

database.bulkInsertOrUpdateReturning(Employees, Triple(Employees.id, Employees.job, Employees.salary)) {
    item {
        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)
    }
    item {
        set(it.id, 5)
        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.