pg-promise using named parameters in an UPDATE SET statement

Question

I want to implement an UPDATE SET statement with named parameters? Is it possible?

For an object like:

{
    _id: 1,
    name: "new_name",
    password: "new_password",
    subscribed: true,
    email: "[email protected]"
}

This is my guess:

UPDATE
    accounts
SET
    $(this:name) = $(this:csv)
WHERE
    _id = $(this._id)

The object fields may vary depending on requests sent, so I don't want to "hard code" the parameters in.

Xylo

Why would you guess like that? There's ton of examples on the library's website, like Learn by Example. You update columns normally, by listing them via comma, according to PostgreSQL syntax. Using this isn't gonna do any magic for you here. – Flue

@Flue I thought maybe there'd be some kind of built in loop that I may have missed. I know from the documentation that this:csv automatically converts it into a kind of .join(",") thing. I was also thinking about using a select $(this:csv) somehow with the update. – Xylo

Each of those filters, :name and :csv do indeed provide some basic automation. But in case of a SET operation, you need a combination. So unless you opt for a multi-row update approach, you will have to use those columns explicitly. – Flue

Answer 1

The only way to automate a SET operation within pg-promise, is if you start using the multi-row update approach, with ColumnSet-s.

// create column set statically, once:
const cs = new pgp.helpers.ColumnSet(['?_id', 'name', 'password', 'subscribed', 'email'],
                                     {table: 'accounts'});

When generating the update query...

const where = pgp.as.format('WHERE _id = $1', [_id]);
const update = `${pgp.helpers.update(data, cs)} ${where}`;

Executing the query:

await db.none(update);

Flue

I'm currently not with my code but do you think something like this would work? UPDATE accounts SET ($(this:name)) = (SELECT $(this:csv); – Xylo

It might, if you try. But it's a different type of query altogether, from the one you asked. – Flue

I would welcome such a feature too, as the OP asked, something like ... SET ${this:combine} WHERE ... and it could generate the comma separated pairs like name1 = value1, name2 = value2,.. based on the json object provided for the statement. – Acheron

@Acheron You can generate assignments, using helpers.sets. – Flue

yes, but I forgot to mention I am using external sql scripts as is suggested in your QueryFile, but now I am tempted to replace all the scripts with this helper one/two liners :) – Acheron

@Acheron You can use both, SQL files and variables in them that are generated SQL. It is quite normal mixed approach. – Flue