CODEWITHSUNDEEP
postgresql
user497849
user497849

Reputation:

How to update field by name?

I have a table of the following form:

id
id_ref
value1
value2
...
valueN

where value1->N are integer fields, and a function that receives an integer parameter "value" that specifies which field needs to be updated, i.e. if "value" param is 2, then only value2 field will be updated.

What I would like to do(in the function), is something like:

CREATE OR REPLACE FUNCTION my_cool_function(value integer) RETURNS bigint AS
...
UPDATE [table name here]
SET "value" || CAST(value[parameter] AS varchar) = [some value]
WHERE [condition];
...
LANGUAGE 'plpgsql'
...

but the above code is not correct, looking for a way to achieve this.

Upvotes: 0

Views: 78

Answers (1)

mu is too short
mu is too short

Reputation: 434665

You can't build identifiers like that but you can use EXECUTE to run some SQL that is in a string:

execute 'update table set '
     || quote_ident('value' || value)
     || ' = '
     || quote_literal('some value')
     || ' where ...';

You may or may not need the quote_ident and quote_literal calls but I'd probably use them to help build good habits. You can avoid the quote_literal with USING:

execute 'update table set '
     || quote_ident('value' || value)
     || ' = $1'
     || ' where ...' using some_value;

Upvotes: 2

Related Questions