Dynamic table name for update

[muriloventuroso]

I have tables with the same structure that are created dynamically over time. Using .TableExpr () I can do the SELECT by setting the table name dynamically. However, when performing an UPDATE the generated query has a syntax error.

Example: err := db.Model(sms1, sms2).TableExpr('sms_2021_01').WherePK().Update()

This code generates an SQL with syntax error.

UPDATE "sms_sms" AS "sms" SET "status_id" = _data."status_id" FROM sms_2021_01(VALUES(7::bigint, 743575737::bigint), ( 7::bigint, 743577934::bigint)) AS _data("status_id", "id") WHERE "sms"."id" = "_data"."id" ERROR: syntax error at or near "("

The correct SQL would be: UPDATE "sms_2021_01" AS "sms" SET "status_id" = _data."status_id" FROM (VALUES(7::bigint, 743575737::bigint), ( 7::bigint, 743577934::bigint)) AS _data("status_id", "id") WHERE "sms"."id" = "_data"."id"

TableExpr is inserting the table name after FROM, when it should replace the table name after UPDATE.

If this is not the correct way to do this operation please help me.

Thank you for the excellent work at this library.

[vmihailenco]

In v11 you will be able to use q.ModelTableExpr("sms_2021_01"). Until then you can use a workaround

type Wrapper struct {
    tableName struct{} `pg:"sms_2021_01"`
    *SomeModel
}

Or you placeholders - https://pg.uptrace.dev/placeholders/#global-db-placeholders

[bwb0101]

you can do this:

table := dbconn.Model(&sms_model{}).TableModel().Table() table.SQLName = types.Safe("sms_2021_01") table.SQLNameForSelects = table.SQLName

it only needs to be initialized once, and can be placed in the relevant init() method