Pass column name as argument - Postgres and Node JS

Question

I have a query (Update statement) wrapped in a function and will need to perform the same statement on multiple columns during the course of my script

async function update_percentage_value(value, id){
  (async () => {
    const client = await pool.connect();
    try {
      const res = await client.query('UPDATE fixtures SET column_1_percentage = ($1) WHERE id = ($2) RETURNING *', [value, id]);
    } finally {
      client.release();
    }
  })().catch(e => console.log(e.stack))
}

I then call this function

update_percentage_value(50, 2);

I have many columns to update at various points of my script, each one needs to be done at the time. I would like to be able to just call the one function, passing the column name, value and id.

My table looks like below

CREATE TABLE fixtures (
  ID SERIAL PRIMARY KEY,
  home_team VARCHAR,
  away_team VARCHAR,
  column_1_percentage INTEGER,
  column_2_percentage INTEGER,
  column_3_percentage INTEGER,
  column_4_percentage INTEGER
);

Is it at all possible to do this?

Yes, it's possible for example using PL/pgSQL to execute dynamic SQL statements. Though, you could also update all the columns in one statement (possibly in batch if data set is huge). Give us sample table structure, data, expected result. — Kamil Gosciminski
– Kamil Gosciminski, Commented Jun 11, 2018 at 19:17
Could you just shed some light on how to pass the column name please at this stage, so in this example column_1_percentage thank you — Richlewis
– Richlewis, Commented Jun 11, 2018 at 19:21
For something I know you need to execute dynamic statement, which means you could make use of anonymous DO code block or wrap your dynamic statement within a Postgres function and then pass parameters to it. Without what I asked for I'm unable to provide you with solution. — Kamil Gosciminski
– Kamil Gosciminski, Commented Jun 11, 2018 at 19:30
Could you please include a sample of what you want to achieve? It's not clear for me as it is. Do you want to call one SQL which will run multiple separate UPDATE statements one for each column that you specify? Please include MVCE in your question so that we could help you out. — Kamil Gosciminski
– Kamil Gosciminski, Commented Jun 11, 2018 at 22:36

halfer · Accepted Answer · 2018-06-15 11:22:18Z

I'm going to post the solution that was advised by Sehrope Sarkuni via the node-postgres GitHub repo. This helped me a lot and works for what I require:

No column names are identifiers and they can't be specified as parameters. They have to be included in the text of the SQL command.

It is possible but you have to build the SQL text with the column names. If you're going to dynamically build SQL you should make sure to escape the components using something like pg-format or use an ORM that handles this type of thing.

So something like:
const format = require('pg-format');

async function updateFixtures(id, column, value) {
  const sql = format('UPDATE fixtures SET %I = $1 WHERE id = $2', column);
  await pool.query(sql, [value, id]);
}
Also if you're doing multiple updates to the same row back-to-back then you're likely better off with a single UPDATE statement that modifies all the columns rather than separate statements as they'd be both slower and generate more WAL on the server.

Karthik · Accepted Answer · 2018-06-12 09:35:17Z

-1

To get the column names of the table, you can query the information_schema.columns table which stores the details of column structure of your table, this would help you in framing a dynamic query for updating a specific column based on a specific result.

You can get the column names of the table with the help of following query:

select column_name from information_schema.columns where table_name='fixtures' and table_schema='public';

The above query would give you the list of columns in the table.

Now to update each one for a specific purpose, You can store the result set of column name to a variable and pass that variable to the function to perform the required action.

answered Jun 12, 2018 at 9:35

Karthik

7692 gold badges9 silver badges12 bronze badges

1 Comment

Richlewis Over a year ago

Thanks for taking the time to answer but please see the answer I posted. I think your answer is not relevant to what I am doing.

Collectives™ on Stack Overflow

Pass column name as argument - Postgres and Node JS

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related