How to use execute dynamic query into int array in Postgresql

Question

We have a trigger function that updates Model Weight when One Part Weight updates.

I want to select list of Model IDs into an int array in postgresql with dynamic query.

Then i want to use this array to find all Models that has this id.

I tried this

CREATE OR REPLACE FUNCTION Func_U_Model_UpdateModelWeightOnPartChange()
    RETURNS TRIGGER
    LANGUAGE plpgsql AS
$$
DECLARE
    model_ids  int[];
BEGIN

    EXECUTE format(
    '
       SELECT DISTINCT m.id
       FROM Part p
       JOIN %1$s A ON A.part_id = p.id
       JOIN Model m ON m.%1$s_id = A.id
       WHERE p.id = %2$s
    ;',
       trim(NEW.part_type),
       NEW.id
    )
    INTO model_ids;

    UPDATE Model
    SET weight = weight + ( NEW.weight - OLD.weight )
    WHERE id IN (model_ids);

    RETURN NULL;
END;
$$;

But I get this error malformed array literal "-9"

I wonder how can I store and query these IDs. I also tried temporary tables but with no chance

This is the trigger

CREATE OR REPLACE TRIGGER Trigger_After_Update_Part_UpdateModelWeight
        AFTER UPDATE OF weight ON Part
        FOR EACH ROW
        EXECUTE FUNCTION Func_U_Model_UpdateModelWeightOnPartChange();

user330315 · Accepted Answer · 2022-01-26 21:31:21Z

1

You need to aggregate the values into an array in order to store them in an array variable.

Additionally: you shouldn't pass parameters as strings, pass them with the USING clause:

EXECUTE format(
'
   SELECT array_agg(DISTINCT m.id)
   FROM Part p
   JOIN %1$s A ON A.part_id = p.id
   JOIN Model m ON m.%1$s_id = A.id
   WHERE p.id = $1
  ',
   trim(NEW.part_type)
)
INTO model_ids    
USING NEW.id;

answered Jan 26, 2022 at 21:31

user330315

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

1 Comment

mrkhosravian Over a year ago

Thank you bro. This totally true. I didn't know about array_agg() function !

mrkhosravian · Accepted Answer · 2022-01-26 22:06:40Z

0

With lots of thanks to @a_horse_with_no_name for his/her answer

I changed my final sql code to this and now works fine.

CREATE OR REPLACE FUNCTION Func_U_Model_UpdateModelWeightOnPartChange()
    RETURNS TRIGGER
    LANGUAGE plpgsql AS
$$
DECLARE
    model_ids  int[];
BEGIN

    EXECUTE format(
    '
        SELECT array_agg(DISTINCT m.id)
        FROM Part p
        JOIN %1$s A ON A.part_id = p.id
        JOIN Model m ON m.%1$s_id = A.id
        WHERE p.id = $1
    ',
    trim(NEW.part_type)
    )
    INTO model_ids    
    USING NEW.id;

    UPDATE Model
    SET weight = weight + ( NEW.weight - OLD.weight )
    WHERE id = ANY (model_ids);

    RETURN NULL;
END;
$$;

answered Jan 26, 2022 at 22:06

mrkhosravian

561 silver badge6 bronze badges

Collectives™ on Stack Overflow

How to use execute dynamic query into int array in Postgresql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related