Update Expressions

[esatterwhite]

I'm looking to expand the update statement. It may be something specific to postgres, but the SET clauses can be expressions

UPDATE table
SET 
  ts = NOW()
, text = SOME_FN( VARIADIC ARRAY(<SUBQUERY>) )

What isn't apparently obvious to me is what can be an expression and what can not be. I guess what I'm looking for is the low level bits that convert the SQL AST objects into the query. is there one?

What does this?

const sql = convertToSQL({
  ':fn()': [ 
     'arg1'
   , {':lower()', {':plain': 'foobar'}}
   , sql.statement('select').where(...)
   ]
})

The more I use this package, the more I see it as a generic sql compiler. The problem I tent to run into is that not everything uses the AST, for lack of a better term.

update set clauses, order by clauses, for example don't allow this, but SQL does.

[jails]

Indeed I didn't explored that far in this direction. I'm using this library to get more expressivity through ORM libraries, not sure if it's possible to support 100% of the SQL syntax through this library.

[esatterwhite]

It's very close. The pieces are there. going back through it, It looks like the escapes function on the dialect class is trying to do this. It wouldn't be much of a stretch to clean that up into a function that compiles sql expressions. Then it would be easier to implement that functionality in different places.

I think it may just be the Update SET expressions and order by expressions that aren't supported currently.

I also had a bit of trouble getting LATERAL joins working.