CODEWITHSUNDEEP
gosqlx
Asnim P Ansari
Asnim P Ansari

Reputation: 2497

How to use UNEST in sqlx posgres named query

I am trying to build a CTE where I want to pass values to the query, convert it a CTE table, and later use that in the subsequent part of the query.

Only CTE part is written below, as that is where the error occurs

const getBasketOrderActionedStatsV2Sql = `

with plans_and_broker_for_user as (
    select
      unnest(:user_ids) as user_id,
      unnest(:broker_ids) as broker_id,
      unnest(:billing_plan_ids) as billing_plan_id
  )

  select * from plans_broker_for_user;
`


    userIds, brokerIds, billingPlanIds := commonqueries.TransformUserBrokerPlanInfoToSeperateArray(userBrokerPlanInfos)

    params := map[string]interface{}{"user_ids": userIds, "broker_ids": brokerIds, "billing_plan_ids": billingPlanIds, "trade_date": tradeDate}

    nstmt, err := appState.PlatformPgConn.PrepareNamed(getBasketOrderActionedStatsV2Sql)

    if err != nil {
        appState.Log.Info("error in preparing named statement")
        return err
    }

Here the PrepareNamed fails with error pq: function unnest(unknown) is not unique

Here userIds, brokerIds, billingPlanIds are arrays respectively.

Some varients of the query I tried are

with plans_and_broker_for_user as (
    select
      unnest(:user_ids::int[]) as user_id,
      unnest(:broker_ids::int[]) as broker_id,
      unnest(:billing_plan_ids::text[]) as billing_plan_id
  )

  select * from plans_broker_for_user;

with plans_and_broker_for_user as (
    select
      unnest((:user_ids)::int[]) as user_id,
      unnest((:broker_ids)::int[]) as broker_id,
      unnest((:billing_plan_ids)::text[]) as billing_plan_id
  )

  select * from plans_broker_for_user;



with ppp as (
select
    user_ids,
    broker_ids,
    billing_plan_ids
from
    unnest(
    :user_ids::integer[],
    :broker_ids::integer[],
    :billing_plan_ids::text[]
) as main_db_data(user_ids,
    broker_ids,
    billing_plan_ids))

select
    *
from
    ppp;

Upvotes: 1

Views: 69

Answers (1)

Asnim P Ansari
Asnim P Ansari

Reputation: 2497

Using UNNEST in the below fashion fixed it.

      unnest(cast(:user_ids as int[])) as user_id,
      unnest(cast(:broker_ids as int[])) as broker_id,
      unnest(cast(:billing_plan_ids as text[])) as billing_plan_id

Upvotes: 0

Related Questions