PostgresSQL: Create a function to update a table value-CodePudding

I need to create an update function for 'visitors' table with 3 arguments (id, column, value).

Here is my code:

CREATE TABLE IF NOT EXISTS museum.visitors (
                             visitor_id serial4 NOT NULL,
                             email varchar(50) NOT NULL,
                             create_date date NOT NULL DEFAULT now(),
                             last_update timestamptz NULL DEFAULT now(),
                             CONSTRAINT visitor_museum_pkey PRIMARY KEY (visitor_id));

CREATE OR REPLACE FUNCTION museum.visitors_row_update(i_id int, i_column TEXT, i_value varchar(50)) 
  RETURNS VOID 
AS $$ 
BEGIN 
  EXECUTE format('UPDATE museum.visitors SET %I = i_value WHERE visitor_id = i_id;') 
      USING email; 
END; $$ 
LANGUAGE plpgsql;

But I get an error: column 'email' does not exist

What's wrong with my code? Could you help me, please? Thank you!

CodePudding user response：

Ok so your problem here is that your code isn't meaning what you think it is meaning.

I think what you mean is

CREATE OR REPLACE FUNCTION museum.visitors_row_update(i_id int, i_column TEXT, i_value varchar(50)) RETURNS VOID AS $$ BEGIN 

    EXECUTE format('UPDATE museum.visitors SET %I = $1 WHERE visitor_id =$2;', i_column) USING i_value, i_id; END; $$ LANGUAGE plpgsql;

Your problem s that format is a function that takes in a series of strings, the first being a format, and the second being string literals that fill it in. So you could do format('....', 'email') but if you don't put single quotes round the "email" then it will assume that is a variable or column name that holds the value you want to put in.

So i_column seems to be what you want instead. And it is in the wrong place (needs to be in the format call), while the using pieces need to refer to parameters returned by the format() call.