sqlx PostgreSQL命名查询中使用UNNEST报错求助

问题根源 #

这个错误是因为PostgreSQL无法确定unnest函数的参数类型——你的命名参数:user_ids、:broker_ids、:billing_plan_ids被识别为unknown类型，而unnest有多个重载版本（支持不同数组类型），数据库无法自动匹配正确的函数。

另外需要注意：你最初的写法中，在SELECT里同时调用多个unnest会产生笛卡尔积（三个数组的所有元素组合），这几乎肯定不是你想要的结果。正确的做法是用多参数unnest（PostgreSQL 9.4+支持），让三个数组按对应位置的元素生成行。

解决方案 #

1. 用Go的pq数组类型明确参数类型

通过github.com/lib/pq提供的数组类型传递参数，让数据库直接识别参数类型，避免类型推断问题：

import "github.com/lib/pq"

// ...

// 将普通切片转换为pq的数组类型
userIdsArr := pq.Int64Array{}
for _, id := range userIds {
    userIdsArr = append(userIdsArr, int64(id))
}

brokerIdsArr := pq.Int64Array{}
for _, id := range brokerIds {
    brokerIdsArr = append(brokerIdsArr, int64(id))
}

billingPlanIdsArr := pq.StringArray(billingPlanIds)

// 构造参数
params := map[string]interface{}{
    "user_ids":         userIdsArr,
    "broker_ids":       brokerIdsArr,
    "billing_plan_ids": billingPlanIdsArr,
    "trade_date":       tradeDate,
}

2. 修正SQL语句（使用多参数unnest）

使用多参数unnest关联三个数组的对应元素，同时不需要额外的类型转换：

const getBasketOrderActionedStatsV2Sql = `
with plans_and_broker_for_user as (
    select
        user_id,
        broker_id,
        billing_plan_id
    from unnest(
        :user_ids,
        :broker_ids,
        :billing_plan_ids
    ) as t(user_id, broker_id, billing_plan_id)
)
select * from plans_and_broker_for_user;
`

备选方案（纯SQL类型转换） #

如果不想依赖pq的数组类型，可以在SQL中明确指定参数类型，但要确保写法正确（用括号包裹参数后再转换）：

const getBasketOrderActionedStatsV2Sql = `
with plans_and_broker_for_user as (
    select
        user_id,
        broker_id,
        billing_plan_id
    from unnest(
        (:user_ids)::integer[],
        (:broker_ids)::integer[],
        (:billing_plan_ids)::text[]
    ) as t(user_id, broker_id, billing_plan_id)
)
select * from plans_and_broker_for_user;
`

这种写法强制数据库将参数转换为指定数组类型，从而匹配正确的unnest重载函数。

内容的提问来源于stack exchange，提问作者Asnim P Ansari