How to update column based on column name in postgres?

Question

I've narrowed it down to two possibilities - DynamicSQL and using a case statement.

However, I've failed with both of these.

I simply don't understand dynamicSQL, and how I would use it in my case.

This is my attempt using case statements; one of many failed variations.

SELECT column_name,
CASE WHEN column_name = 'address' THEN (**update statement gives syntax error within here**)
END
FROM information_schema.columns
WHERE table_name = 'employees';

As an overview, I'm using Axios to talk to my Node server, which is making calls to my Heroku database using Massivejs.

Maybe this isn't the way to go - so here's my main problem:

I've ran into troubles because the values I'm planning on using as column names are sent to my server as strings. The exact call that I've been trying to use is

update employees
set $1 = $2
where employee_id = $3;

Once again, I'm passing into those using massive.

I get the error back { error: syntax error at or near "'address'"} because my incoming values are strings. My thought process was that the above statement would allow me to use variables because 'address' is encapsulated by quotes.

But alas, my thought process has failed me.

This seems to be close to answering my question, but I can't seem to figure out what to do in my case if using dynamic SQL.

How to use dynamic column names in an UPDATE or SELECT statement in a function?

Thanks in advance.

Answer 1

I will show you a way to do this by using a function. First we create the employees table :

CREATE TABLE employees(
id BIGSERIAL PRIMARY KEY,
column1 TEXT,
column2 TEXT
);

Next, we create a function that requires three parameters:

columnName - the name of the column that needs to be updated

columnValue - the new value to which the column needs to be updated

employeeId - the id of the employee that will be updated

By using the format function we generate the update query as a string and use the EXECUTE command to execute the query.

Here is the code of the function.

CREATE OR REPLACE FUNCTION update_columns_on_employee(columnName TEXT, columnValue TEXT, employeeId BIGINT)
  RETURNS VOID AS
  $$
  DECLARE update_statement TEXT := format('UPDATE EMPLOYEES SET %s = ''%s'' WHERE id = %L',columnName, columnValue, employeeId);
  BEGIN
    EXECUTE update_statement;
end;
$$ LANGUAGE plpgsql;

Now, lets insert some data into the employees table

  INSERT INTO employees(column1, column2) VALUES ('column1_start_value','column2_start_value');

So now we currently have an employee with an id value of 1 who has 'column1_start_value' value for the column1, and 'column2_start_value' value for column2.

If we want to update the value of column2 from 'column2_start_value' to 'column2_new_value' all we have to do is execute the following call

SELECT * FROM update_columns_on_employee('column2','column2_new_value',1);