search

crysalead-js / sql-dialect

SQL Builder
MIT License
6 stars 2 forks source link

Update Expressions #9

Open esatterwhite opened 4 years ago

esatterwhite commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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.