我有一個 postgres 執(zhí)行字符串。我有一排用戶，有一系列舉重數(shù)據(jù)。我想更新當(dāng)天的日志（即，如果今天已經(jīng)記錄了電梯，則替換最后一個數(shù)組元素），或者將提升數(shù)據(jù)附加到這些數(shù)組中。BEGINIF (SELECT date[array_upper(date, 1)] FROM userdata WHERE email = $8) = $7 THENUPDATE userdata SET age = $1,weight[array_upper(weight, 1)] = $2,deadlift[array_upper(deadlift, 1)] = $3,squat[array_upper(squat, 1)] = $4,bench[array_upper(bench, 1)] = $5,overhead[array_upper(overhead, 1)] = $6,WHERE email = $8;ELSEUPDATE userdata SET age = $1,weight = array_append(weight, $2),deadlift = array_append(deadlift, $3),squat = array_append(squat, $4),bench = array_append(bench, $5),overhead = array_append(overhead, $6),date = array_append(date, $7)WHERE email = $8;END IF;END;首先檢查日期（即最后一個日期數(shù)組元素）是否 == 今天的日期，如果為真，則將最后一個元素設(shè)置為給定值。否則，將給定值附加到所有這些數(shù)組的末尾我的 Golang 代碼如下：_, err := d.conn.Exec(context.Background(), execstring, user.Age, user.Weight, user.Deadlift, user.Squat, user.Bench, user.Overhead, fmt.Sprint(time.Now().Date()), user.Email)使用以下變量執(zhí)行 execstring（第一個代碼塊）。錯誤：錯誤：“IF”處或附近的語法錯誤golang 和 postgres 的新手，希望得到一些幫助（問題可能出在我的 execstring 上）編輯（下面的案例版本）：SELECT date[array_upper(date, 1)]CASEWHEN date[array_upper(date,1)] = $7 THENUPDATE userdata SET age = $1,weight[array_upper(weight, 1)] = $2,deadlift[array_upper(deadlift, 1)] = $3,squat[array_upper(squat, 1)] = $4,bench[array_upper(bench, 1)] = $5,overhead[array_upper(overhead, 1)] = $6,WHERE email = $8;ELSEUPDATE userdata SET age = $1,weight = array_append(weight, $2),deadlift = array_append(deadlift, $3),squat = array_append(squat, $4),bench = array_append(bench, $5),overhead = array_append(overhead, $6),date = array_append(date, $7)WHERE email = $8;ENDFROM userdata WHERE email = $8錯誤：錯誤：“CASE”處或附近的語法錯誤
1 回答
慕容708150
TA貢獻(xiàn)1831條經(jīng)驗 獲得超4個贊
這是我為解決我的問題所做的（希望它可以幫助其他嘗試做類似事情的人）
似乎 CASE 不能用于決定運行哪個語句，而是似乎只決定將哪些文本放入查詢/執(zhí)行字符串中。因此，我必須四處移動才能做到這一點，使用 CASE 來決定將我的數(shù)組設(shè)置為等于什么，而不是決定對數(shù)組執(zhí)行什么操作。
UPDATE userdata
SET age = $1,
weight = CASE WHEN date[array_upper(date,1)] = $7 THEN array_replace(weight, weight[array_upper(weight, 1)], $2)
ELSE array_append(weight, $2) END,
deadlift = CASE WHEN date[array_upper(date,1)] = $7 THEN array_replace(deadlift, deadlift[array_upper(deadlift, 1)], $3)
ELSE array_append(deadlift, $3) END,
squat = CASE WHEN date[array_upper(date,1)] = $7 THEN array_replace(squat, squat[array_upper(squat, 1)], $4)
ELSE array_append(squat, $4) END,
bench = CASE WHEN date[array_upper(date,1)] = $7 THEN array_replace(bench, bench[array_upper(bench, 1)], $5)
ELSE array_append(bench, $5) END,
overhead = CASE WHEN date[array_upper(date,1)] = $7 THEN array_replace(overhead, overhead[array_upper(overhead, 1)], $6)
ELSE array_append(overhead, $6) END,
date = CASE WHEN date[array_upper(date,1)] = $7 THEN array_replace(date, date[array_upper(date, 1)], $7)
ELSE array_append(date, $7) END
WHERE email = $8;
- 1 回答
- 0 關(guān)注
- 157 瀏覽
添加回答
舉報
0/150
提交
取消