If/Else 语句中的 Golang Postgres 语法错误

Question

我有一个 postgres 执行字符串。我有一排用户，有一系列举重数据。我想更新当天的日志（即，如果今天已经记录了电梯，则替换最后一个数组元素），或者将提升数据附加到这些数组中。

BEGIN

IF (SELECT date[array_upper(date, 1)] FROM userdata WHERE email = $8) = $7 THEN

UPDATE 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;

ELSE

UPDATE 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;

首先检查日期（即最后一个日期数组元素）是否 == 今天的日期，如果为真，则将最后一个元素设置为给定值。

否则，将给定值附加到所有这些数组的末尾

我的 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)

使用以下变量执行 execstring（第一个代码块）。

错误：错误：“IF”处或附近的语法错误

golang 和 postgres 的新手，希望得到一些帮助（问题可能出在我的 execstring 上）

编辑（下面的案例版本）：

SELECT date[array_upper(date, 1)]

CASE

WHEN date[array_upper(date,1)] = $7 THEN

UPDATE 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;

ELSE

UPDATE 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

FROM userdata WHERE email = $8

错误：错误：“CASE”处或附近的语法错误

Answer 1

这是我为解决我的问题所做的（希望它可以帮助其他尝试做类似事情的人）似乎 CASE 不能用于决定运行哪个语句，而是似乎只决定将哪些文本放入查询/执行字符串中。因此，我必须四处移动才能做到这一点，使用 CASE 来决定将我的数组设置为等于什么，而不是决定对数组执行什么操作。UPDATE userdataSET 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) ENDWHERE email = $8;