1

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: "new_email@email.com"
}

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.

A. L
  • 11,695
  • 23
  • 85
  • 163
  • Why would you guess like that? There's ton of examples on the library's website, like [Learn by Example](https://github.com/vitaly-t/pg-promise/wiki/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. – vitaly-t Dec 29 '19 at 15:54
  • @vitaly-t 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. – A. L Dec 29 '19 at 16:03
  • 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](https://stackoverflow.com/questions/39119922/postgresql-multi-row-updates-in-node-js), you will have to use those columns explicitly. – vitaly-t Dec 30 '19 at 15:38

1 Answers1

2

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);
vitaly-t
  • 24,279
  • 15
  • 116
  • 138
  • I'm currently not with my code but do you think something like this would work? `UPDATE accounts SET ($(this:name)) = (SELECT $(this:csv);` – A. L Dec 31 '19 at 05:06
  • It might, if you try. But it's a different type of query altogether, from the one you asked. – vitaly-t Dec 31 '19 at 17:44
  • 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. – Zavael Feb 06 '20 at 09:17
  • @Zavael You can generate assignments, using [helpers.sets](http://vitaly-t.github.io/pg-promise/helpers.html#.sets). – vitaly-t Feb 06 '20 at 11:01
  • yes, but I forgot to mention I am using external sql scripts as is suggested in your [QueryFile](http://vitaly-t.github.io/pg-promise/QueryFile.html), but now I am tempted to replace all the scripts with this helper one/two liners :) – Zavael Feb 06 '20 at 13:09
  • @Zavael You can use both, SQL files and variables in them that are generated SQL. It is quite normal mixed approach. – vitaly-t Feb 06 '20 at 16:45