Dynamic column names in the Postgres query

Question

I have one table with a bunch of fields. For simplicity, assume 5 fields:

unique_id, currency_name, stock_description, company_name, city_name

say, the sample values are:

INTQL123, USD,  "INT Stock", "International Stock Corporation", "CHICAGO"

And another one called override_table that can contain the override values for any column:

unique_id, override_column, override_value

Sample values:

INTQL123, "city_name", "NEW YORK"

So it is overriding the value of the city "CHICAGO" with the new value "NEW YORK"

I want to be able to write an SQL UPDATE statement with such dynamic column names. We can assume they will only be of the varchar type. Any idea?

Answer 1

Yoy can achieve this with an anonymous PL/pgSQL block and dynamic SQL.

do language plpgsql
$$
declare
  r record;
  DYNSQL constant text := 'UPDATE the_table SET %I = %L WHERE unique_id = %L';
begin
  for r in select * from override_table loop
    execute format(DYNSQL, r.override_column, r.override_value, r.unique_id);
  end loop;
end;
$$;

The code above is SQLi prone. Make sure that no malicious values exist in table override_table before running it.

Edit
"is there a way to return the number of updates back to the caller?"
Anonymous do blocks do not return anything. Two approaches come to my mind.

• You can shape the code above as a function with a counter, say dyn_update, and issue select dyn_update() from Java;

CREATE OR REPLACE FUNCTION dyn_update()
returns integer language plpgsql as
$$
declare
  r record;
  DYNSQL constant text := 'UPDATE the_table SET %I = %L WHERE unique_id = %L';
  retval integer := 0;
begin
  for r in select * from override_table loop
    execute format(DYNSQL, r.override_column, r.override_value, r.unique_id);
    retval := retval + 1;
  end loop;
  return retval;
end;
$$;

• select count(*) from override_table as it would return exactly the number of updates (unless the do block bangs an exception)