0
votes

I'm working with PostgreSQL 9.5.

I'm creating a trigger in PL/pgSQL, that adds a record to a table (synthese_poly) when an INSERT is performed on a second table (operation_poly), with other tables data.

The trigger works well, except for some variables, that are not filled (especially the ones I try to fill with an array_to_string() function).

This is the code:

-- Function: bdtravaux.totablesynth_fn()
-- DROP FUNCTION bdtravaux.totablesynth_fn();

CREATE OR REPLACE FUNCTION bdtravaux.totablesynth_fn()
  RETURNS trigger AS
$BODY$

 DECLARE 
    varoperateur varchar;
    varchantvol boolean;

BEGIN
IF (TG_OP = 'INSERT') THEN
varsortie_id := NEW.sortie;
varopeid := NEW.operation_id;

--The following « SELECT » queries take data in third-party tables and fill variables, which will be used in the final insertion query.

SELECT array_to_string(array_agg(DISTINCT oper.operateurs),'; ')
INTO varoperateur
FROM bdtravaux.join_operateurs oper INNER JOIN bdtravaux.operation_poly o ON (oper.id_joinop=o.id_oper) 
WHERE o.operation_id = varopeid;

SELECT CASE WHEN o.ope_chvol = 0 THEN 'f' ELSE 't' END as opechvol INTO varchantvol 
FROM bdtravaux.operation_poly o WHERE o.operation_id = varopeid;

-- «INSERT» query
INSERT INTO bdtravaux.synthese_poly (soperateur, schantvol)  SELECT  varoperateur, varchantvol;

RAISE NOTICE  'varoperateur value : (%)', varoperateur;
RAISE NOTICE  'varchantvol value : (%)', varchantvol;

END IF;
RETURN NEW;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION bdtravaux.totablesynth_fn()
  OWNER TO postgres;

And this is the trigger :

-- Trigger: totablesynth on bdtravaux.operation_poly
-- DROP TRIGGER totablesynth ON bdtravaux.operation_poly;

CREATE TRIGGER totablesynth
  AFTER INSERT
  ON bdtravaux.operation_poly
  FOR EACH ROW
  WHEN ((new.chantfini = true))
  EXECUTE PROCEDURE bdtravaux.totablesynth_fn();

The varchantvol variable is correctly filled, but varoperateur stays desperately empty (NULL value) (and so on for the corresponding field in the synthese_poly table).

Note:
The SELECT array_to_string(…) ... query itself (launched with pgAdmin, without INTO varoperateur and replacing varopeid with a value) works well, and returns a string.

I tried to change array_to_string() function and variables' data types (using ::varchar or ::text …), nothing works. Do you see what can happen?

2
A trigger function needs to be considered in combination with the actual trigger, which is missing. - Erwin Brandstetter
Thank you for the advice. I've added the trigger. - V Damoy

2 Answers

2
votes

using array_agg

You can replace array_to_string(array_agg(DISTINCT oper.operateurs),'; ') with

string_agg(DISTINCT oper.operateurs,'; ')

And you can use order by to sort the text in the agregate

string_agg(DISTINCT oper.operateurs,'; ' ORDER BY oper.operateurs)
1
votes

My educated guess: you have a trigger with BEFORE INSERT ON bdtravaux.operation_poly. And operation_id is its serial PK column.

In this case, the query with WHERE o.operation_id = varopeid (where varopeid has been filled with NEW.operation_id) can never find any rows because the row is not in the table, yet.

array_agg() has no role in this.

Would work with a trigger AFTER INSERT ON bdtravaux.operation_poly. But if id_oper is from the same inserted row, you can just simplify to:

SELECT array_to_string(array_agg(DISTINCT oper.operateurs),'; ')
INTO   varoperateur
FROM   bdtravaux.join_operateurs oper
WHERE  oper.id_joinop = NEW.id_oper;

And keep the BEFORE trigger.

The whole function might be simpler, can probably done with a single query.