Hey! In SQLDelight I was wondering if it was possible to get the last inserted rowid of another tabl...

# squarelibraries

[Tech]

Hey! In SQLDelight I was wondering if it was possible to get the last inserted rowid of another table, I remember doing something like this with a function within a grouped statement but I can't seem to redo it with Postgres.

Thank you!

[Arjan van Wieringen]

“INSERT INTO persons (lastname,firstname) VALUES ('Smith', 'John') RETURNING id;”?

[Tech]

insert {
// last_insert_rowid()
val empId = INSERT INTO employees(id) VALUES (DEFAULT) RETURNING id;

INSERT INTO Company(employeeId) VALUES (empId);

}

Basically I need to pass the id of one inserted object to another in the same transaction block

[Tech]

i know it’s possible but it doesn’t seem like the SQLite syntax works with Postgres

[Arjan van Wieringen]

Doesn’t this work? https://github.com/cashapp/sqldelight/issues/1015

[griffio]

🤔 A few options for Postgres if they are suitable 🐘
• Use a transaction to run the two statements - first one returns the new id https://cashapp.github.io/sqldelight/2.0.0/jvm_postgresql/transactions/
• Use a block with currval e.g

insert: {
INSERT INTO employee(name) VALUES (?);
INSERT INTO company(employee_id) SELECT currval('employee_id_seq');
}

• Use CTE (Common Table Expressions - see https://github.com/griffio/sqldelight-postgres-04

insert:
WITH new_employee AS (
    INSERT INTO employee(name) VALUES (?) RETURNING employee_id
),
new_company_employee AS (
    INSERT INTO company(employee_id)
    SELECT employee_id
    FROM new_employee
    ON CONFLICT DO NOTHING
    RETURNING *
)
SELECT * FROM new_company_employee JOIN new_employee USING (employee_id);