Is there any way to emulate UNNEST?

[maxguuse]

I'm currently migrating my repositories from SQLC to go-jet and I need to somehow translate this query to jet syntax

const createPollOptions = `-- name: CreatePollOptions :many
INSERT INTO poll_options ("title", "poll_id") 
VALUES (UNNEST($1::varchar[]), $2)
RETURNING poll_options.id, poll_options.title, poll_options.poll_id
`

I tried to do something like this

err := table.PollOptions.INSERT(
	table.PollOptions.Title,
	table.PollOptions.PollID,
).VALUES(
	postgres.Raw("UNNEST($1::varchar[]", map[string]any{
		"$1": pollOptions,
	}),
	pollID,
).RETURNING(
	table.PollOptions.AllColumns,
).QueryContext(ctx, db, dest)

but it doesn't work and panics saying that []string cannot be used as sql parameter, am I missing something?

[maxguuse]

I'm using this temporary solution for now to just encode slice in postgres format, but I'd like to know if there's some builtin way to do it

var quoteArrayReplacer = strings.NewReplacer(`\`, `\\`, `"`, `\"`)

func quoteArrayElement(src string) string {
	return `"` + quoteArrayReplacer.Replace(src) + `"`
}

func encodeStringsSlice(strs []string) string {
	for i, str := range strs {
		strs[i] = quoteArrayElement(str)
	}

	return fmt.Sprintf("{%s}", strings.Join(strs, ","))
}

[houten11]

You can't use []string as a parameter, you'll need to use string array that knows how to serialize to Postgres array type. For instance pq.StringArray

err := table.PollOptions.INSERT(
	table.PollOptions.Title,
	table.PollOptions.PollID,
).VALUES(
	postgres.Raw("UNNEST($1::varchar[]", map[string]any{
		"$1": pq.StringArray(pollOptions),
	}),
	pollID,
).RETURNING(
	table.PollOptions.AllColumns,
).QueryContext(ctx, db, dest)

Additionaly, you can define UNNEST function:

func UNNEST(arr []string) Expression {
	return Func("UNNEST", Raw("#1::varchar[]", map[string]any{
		"#1": pq.StringArray(arr),
	}))
}

and then write:

err := table.PollOptions.INSERT(
	table.PollOptions.Title,
	table.PollOptions.PollID,
).VALUES(
	UNNEST(pollOptions),
        pollID,
).RETURNING(
	table.PollOptions.AllColumns,
).QueryContext(ctx, db, dest)