Using filters in Update statement

[andrejb-dev]

Expected behavior

Generate dynamic Update statement from json object like {testName:tesVal, testName2:testVal2} with some filters inside query.

Actual behavior

I think now it is not possible to enumerate multiple key/value pairs to match UPDATE statement requirements. Filters like 'INSERT INTO table(${this:name}) VALUES(${this:list})' cannot be used becouse they separate keys from values. I created a simple function for myself that converts stringyfied object content tu update string statement

function nonEmptyColumnsToUpdate(string) {
    return string
        .slice(1, string.length - 1)
        .replace(/"/g, '')
        .replace(/:/g, ' = ')
}

It would be fine to use something like this whit filters like t.none('UPDATE users SET $1:pairs WHERE id = $2', [{json:object, some:params}, 123]) Or is there a way to do this that I missed?

• Version of pg-promise: 10.5.8
• OS type (Linux/Windows/Mac): win
• Version of Node.js: 10.16

[vitaly-t]

Or is there a way to do this that I missed?

Yes, there is Custom Type Formatting.

I even would write an example, but I'm not sure what your $1:pairs is expected to produce from an object. Can you provide a complete example?

[vitaly-t]

@Zavael Any update?

[andrejb-dev]

@vitaly-t I have no complete example as I chose the path of using my function to provide raw string, for now. With $1:pairs (or other suitable keyword) I would make use of transformation of this

t.none('UPDATE users SET $1:pairs WHERE id = $2', [{name: "John", address_id: 15}, 123])

to this:

UPDATE users
SET
  name = John, address_id = 15
WHERE id = 123;

I often use QueryFiles and it would be great to just send a object as query param and pg-promise would generate assignments of all object's properties that are present inside the SET block of UPDATE query.

I tried to read about CTF but not sure how to use it.

[vitaly-t]

I tried to read about CTF but not sure how to use it.

Which part is not clear? It has easy-to-read documentation, and with examples.

[andrejb-dev]

the docs are clear and very helpful indeed. But I am not using typescript, only vanila JS and I have no class definition for the data object that I am getting from the ui, so I have no space to put toPostgres() implementation

[vitaly-t]

The docs show you examples in JavaScript.