Postgres Pass column name as trigger-CodePudding

I am trying to create a trigger on my table that shall sort a column of array. But I want to make it dynamic by passing column name as parameter

CREATE OR REPLACE FUNCTION my_table_sort_array() RETURNS trigger AS
$BODY$
DECLARE column_name   text     := TG_ARGV[0];
begin
 EXECUTE format('NEW.$1 = sort_array($1)', column_name);
  RETURN NEW;
END;
$BODY$ LANGUAGE 'plpgsql';

CREATE TRIGGER my_table_sort_array_trigger
BEFORE INSERT OR UPDATE ON my_table
FOR EACH ROW EXECUTE PROCEDURE my_table_sort_array('array_column');

Table:

CREATE TABLE my_table (
    id varchar(255) NOT NULL,
    text_column varchar(255) NOT NULL,
    array_column _text NOT NULL,
    CONSTRAINT my_table_pkey PRIMARY KEY (id)
);

insert into my_table values ('1', 'a', '{b,a}');

But this is failing with the given error

SQL Error [42601]: ERROR: syntax error at or near "NEW"
  Where: PL/pgSQL function my_table_sort_array() line 4 at EXECUTE


Error position:

Can you please let me know how to achieve that

CodePudding user response：

If you are willing to use plpython3 then the below. I don't know where sort_array() is coming from so I used array_dims() instead for illustrative purposes:

CREATE OR REPLACE FUNCTION public.my_table_sort_array()
 RETURNS trigger
 LANGUAGE plpython3u
AS $function$
col_name = TD['args'][0]
array_val = TD['new'][col_name]
plan = plpy.prepare("select array_dims($1)", ["text[]"] )
rs = plpy.execute(plan, [array_val])
plpy.notice(rs[0])
$function$
;

insert into my_table values ('1', 'a', '{b,a}');
NOTICE:  {'array_dims': '[1:2]'}
INSERT 0 1