How to use execute dynamic query into int array in Postgresql-CodePudding

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();

CodePudding user response：

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;

CodePudding user response：

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;
$$;