Unable to generate queries using "SET" and "SET LOCAL"

[Pitasi]

Version

1.30.0

What happened?

I'm trying to generate a query using one of the following variations:

SET LOCAL app.current_user_id = @user_id::text;
-- syntax error at or near "@"

SET LOCAL app.current_user_id to @user_id::text;
-- syntax error at or near "@"

SET LOCAL app.current_user_id = sqlc.arg(user_id)::text;
-- syntax error at or near "."

SET LOCAL app.current_user_id = $1::text;
-- syntax error at or near "::"

SET LOCAL app.current_user_id to cast($1 as text);
-- syntax error at or near "cast"

Eventually, I found that this query works:

SET LOCAL app.current_user_id = $1;

and generates this Go code:

const setSessionUser = `-- name: SetSessionUser :exec
SET LOCAL app.current_user_id = $1
`

func (q *Queries) SetSessionUser(ctx context.Context, dollar_1 interface{}) error {
	_, err := q.db.Exec(ctx, setSessionUser, dollar_1)
	return err
}

Which is not great, but good enough as a workaround.

Relevant log output

Database schema

SQL queries

Configuration

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[Pitasi]

I also found out that in pgx you need to add pgx.QueryExecModeSimpleProtocol, like that:

	if _, err := tx.Exec(ctx, "SET LOCAL foo = $1", pgx.QueryExecModeSimpleProtocol, 42); err != nil {
		panic(err)
	}

since prepared statements don't work for SET and SET LOCAL.

So my workaround doesn't actually work 😅 The only way was to use pgx directly.