can't figure out how to use CTE and values

[gstarikov]

Hi, give please advise, how to implement query with 'with' and 'values' closures via bob?
psql query:

    with c(id, data) as (values ('1'::bigint, 'data 1'),
                                   ('2'::bigint, 'data 2'),
                                   ('123456789'::bigint, 'data 3'))
    update test as t
    set data = c.data
    from c
    where c.id = t.id

[stephenafamo]

The main reason why this is not possible is that Bob currently does not yet have a way to build VALUES queries. Only SELECT, UPDATE, INSERT and DELETE.

Since we already have the syntax down for inserts, I could try and make this work 🤔 .

In the meantime, here is a way you can do this, relying on clause.Values

vals := clause.Values{}
vals.AppendValues(psql.S("1"), psql.S("data 1"))
vals.AppendValues(psql.S("2"), psql.S("data 2"))
vals.AppendValues(psql.S("3"), psql.S("data 3"))

psql.Update(
  um.With("c", "id", "data").As(bob.BaseQuery[clause.Values]{
    Expression: vals,
    Dialect: dialect.Dialect,
  }),
  um.TableAs("test", "t"),
  um.Set("data").To("c.data"),
  um.From("c"),
  um.Where(psql.Quote("c", "id").EQ(psql.Quote("t", "id"))),
)

NOTE: I didn't actually run this code, but I bet it should work. Try it out and let me know.

[gstarikov]

nice,
I updated the code a little (AppendValues instead of Append)
and got result, near to expected

    WITH c AS (VALUES ('1', 'data 1'), ('2', 'data 2'), ('3', 'data 3'))
    UPDATE test AS   t
    SET   data   = c.data
    FROM c
    WHERE (  c.id = t.id )
    ...
    column c.id does not exist (SQLSTATE 42703)

There seems to be a bug - the WriteSQL CTE method (item/cte.go:19) ignores the Columns property and does not insert their values into the resulting SQL.

[stephenafamo]

Should be fixed in 9066cac
Try it out and let me know

[gstarikov]

yes, it works

query="\nWITH\nc(id,

[gstarikov]

BTW how to specify type and how to work with nullable types in case of values ?
right now i did it in that way:

vals.AppendValues(
	psql.Raw(`?::bigint`, intID),
	...
	psql.Raw(`?::bigint`, driverValue),

and nullable i converted as: type -> omitnull.Val -> .Value() -> got driver.Value and pass it into psql.Raw

[stephenafamo]

and nullable i converted as: type -> omitnull.Val -> .Value() -> got driver.Value and pass it into psql.Raw

You don't need to call .Value() yourself, the database driver would do this for you. Also, you shouldn't need omitnull.Val, null.Vall should be enough.

For type casting (::bigint) there is currently no builtin way to do this. But perhaps I can add a Cast method to the psql builder so that you can do.

psql.Arg(intID).CastAs("bigint")