Dynamic column name in function

Question

How do I create dynamic column names, that can be used in a trigger? I have a trigger function, that should update a value in a table. The columnname is created dynamicially. But when I run it, the error does not grasp the column name but instead the name of the variable. What the code should do was to create a column name example: p123

ERROR: column "column_name" of relation "col_extra_val_lookup" does not exist LINE 2: SET column_name = NEW.value

The code I use is this:

 DECLARE
column_name TEXT ;
BEGIN
column_name  :='p'|| NEW.customer_column_id;

UPDATE col_extra_val_lookup
SET column_name = NEW.value 
WHERE col_extra_val_lookup.customer_id=NEW.customer_id;
END

Belayer · Accepted Answer · 2020-05-03 00:50:11Z

You get values because that is the only thing referencing a data point is able to give. To get what your looking for you need dynamic SQL. What you are attempting is take a data value and create a column with that value (prefixed with 'p'). This is really a very bad plan. Since you have no way of knowing the value of customer_column_name before your trigger must also alter the table. But unless you have very strict control over that value you can wind up with very strange column names.

The following shows one way of accomplishing what you requested.
But before you do this I suggest you look here to see just what can happen, and if you are prepared to handle the possible consequences, at least with its current limitations (code).

create or replace function really_bad_idea()
  returns trigger 
  language plpgsql
as $$
declare 
    k_add_column   constant text =
      'alter table col_extra_val_lookup add column if not exists %I text';
    k_update_lookup constant text = 
      'update col_extra_val_lookup set %I = $1 where customer_id = $2'; 

    column_name character varying(63);  -- normal Postgres max length for column name
begin 
    column_name = 'p'|| NEW.customer_column_id;
    execute format(k_add_column, column_name);
    execute format(k_update_lookup, column_name) using new.value, new.customer_id;
    return new;
end; 
$$;

Enjoy your side effects.

Collectives™ on Stack Overflow

Dynamic column name in function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related