Postgres Pass column name as trigger

Question

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

I don't think there is a way to do that.

Laurenz Albe
– Laurenz Albe

2022-12-05 10:31:49 +00:00
Commented Dec 5, 2022 at 10:31 — Laurenz Albe
– Laurenz Albe, Commented Dec 5, 2022 at 10:31
Yeah, great question. I'll walk around and listen too.

danissimo
– danissimo

2023-01-13 03:25:26 +00:00
Commented Jan 13, 2023 at 3:25 — danissimo
– danissimo, Commented Jan 13, 2023 at 3:25

Adrian Klaver · Accepted Answer · 2022-12-05 23:51:26Z

0

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

answered Dec 5, 2022 at 23:51

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres Pass column name as trigger

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related