Insert trigger in postgresql, dynamic statement

Question

I am trying to add an insert trigger to a table in order to implement partitioning. The following code works:

CREATE OR REPLACE FUNCTION item_lines_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO item_lines_partitions.p11_1 VALUES (NEW.*);
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

My problem - is that when I try to make it dynamic (so that I can redirect to a table based on the values that I get), I just can't insert the record (NEW.*) into a dynamic statement. Here is what I Tried to do:

CREATE OR REPLACE FUNCTION item_lines_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    EXECUTE format('INSERT INTO item_lines_partitions.p%s_%s VALUES ',NEW.tenant_id,NEW.store_id) || quote_literal(NEW.*);
    RETURN NULL;
END;

I get a syntax error as following with the dynamic statement: PG::SyntaxError: ERROR: syntax error at or near "'(49563,,1,11,100125,100125,1,,...

I also tried using EXECUTE <expression ... $1> USING NEW.* but it did not work as well.

Any ideas on how to insert NEW.* expression into a dynamic statement?

Thanks!

whats wrong with just using standard Table Partitioning: postgresql.org/docs/current/… ? — Fedor
– Fedor, Commented May 6, 2024 at 15:01

Laurenz Albe · Accepted Answer · 2021-03-19 18:04:41Z

1

Try this:

EXECUTE
   format(
      'INSERT INTO item_lines_partitions.%I SELECT ($1::text::item_lines_partitions.%I).*',
      'p' || NEW.tenant_id || '_' || NEW.store_id,
      'p' || NEW.tenant_id || '_' || NEW.store_id
   )
   USING NEW;

Here, NEW is used as a parameter to the statement and type cast to the appropriate table type.

The way you construct the table string was not safe, so I changed that.

edited Mar 19, 2021 at 18:04

answered Mar 19, 2021 at 17:02

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

Rubinsh Over a year ago

Thanks. It did not work. I got the following error: PG::CannotCoerce: ERROR: cannot cast type item_lines to item_lines_partitions.p11_1 where item_lines is the parent table name (I do partitioning with table inheritance)

Rubinsh Over a year ago

Now I get the following:

ActiveRecord::StatementInvalid (PG::DatatypeMismatch: ERROR:  column "id" is of type integer but expression is of type item_lines_partitions.p11_1)

I Assume this is because I have many column types in that table. Thank you very much for your help. I can provide the table schema if that helps, but in general I have datatypes of text, integer, float, date, and time

Collectives™ on Stack Overflow

Insert trigger in postgresql, dynamic statement

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related