Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

Description

Scenes:

• Database: Postgresql
• Table structure: Use other fields that do not contain ID(int64, auto_increment) as composite primary keys, eg: (Name, ProjectId)
• Association: ID will be used as a foreign key association

Example:

_,err:=dao.Company.
OmitEmpty().
OnConflict(
dao.Company.Columns().Name,
dao.Company.Columns().ProjectId,
).
OnDuplicateEx(
dao.Company.Columns().Id,
dao.Company.Columns().Name,
dao.Company.Columns().ProjectId,
dao.Company.Columns().CreateTime,
).Save(companies)

need:

• Need to use Save() to perform Upsert operation and obtain LastInsertId
• Don’t want to write SQL RAW statements

question:

• Save() is actually executed as an INSERT INTO statement in postgresql. The Insert() operation will add a RETURNING statement, but Save() will not
• Insert() operation cannot obtain the return value of LastInsertId() in the scenario of composite primary key that does not contain ID int64. In the above scenario, the following statement will be executed:INSERT INTO... RETURNING "name",when executing LastInsertId() Will returnLastInsertId is not supported by primary key typeerror

Temporary solution:

As you can see from the following code, you can control the fields returned by the RETURNING statement by configuring the internalPrimaryKeyInCtx value in ctx

• gogf/gf/contrib/drivers/pgsql/[email protected]/pgsql_do_exec.go

const(
internalPrimaryKeyInCtxgctx.StrKey="primary_key"
)
// Check if it is an insert operation with primary key.
ifvalue:=ctx.Value(internalPrimaryKeyInCtx);value!=nil{
varOKbool
pkField,ok=value.(gdb.TableField)
if!ok{
isUseCoreDoExec=true
}
}else{
isUseCoreDoExec=true
}

// check if it is an insert operation.
if!isUseCoreDoExec&&pkField.Name!=""&&strings.Contains(sql,"INSERT INTO") {
primaryKey=pkField.Name
sql+=fmt.Sprintf(` RETURNING "%s" `,primaryKey)
}else{
// use default DoExec
returnd.Core.DoExec(ctx,link,sql,args...)
}

The following code shows that the LastInsertId() operation requires RETURNING that the fields in xxx need to be of type int

• gogf/gf/contrib/drivers/pgsql/[email protected]/pgsql_do_exec.go

affected:= len(out.Records)
if affected > 0 {
if!strings.Contains(pkField.Type, "int" ) {
return Result{
affected: int64(affected),
lastInsertId: 0,
lastInsertIdError: gerror.NewCodef(
gcode.CodeNotSupported,
"LastInsertId is not supported by primary key type: %s", pkField.Type),
}, nil
}
...

Therefore, after changing the code to the following, LastInsertId() can be called normally:

result, err:= dao.Company.
Ctx(context.WithValue(ctx, consts.InternalPrimaryKeyInCtx, gdb.TableField{Name: "id", Type: "int64" })). // new add
OmitEmpty().
OnConflict(
dao.Company.Columns().Name,
dao.Company.Columns().ProjectId,
).
OnDuplicateEx(
dao.Company.Columns().Id,
dao.Company.Columns().Name,
dao.Company.Columns().ProjectId,
dao.Company.Columns().CreateTime,
).Save(companies)
if err!= nil {
return
}
id, err = result.LastInsertId()

Summarize:

• Whether relevant parameters can be provided to control the RETURNING field

Additional

No response