Fill a variable with "array_to_string" in a plpgsql trigger function

Question

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?

A trigger function needs to be considered in combination with the actual trigger, which is missing. — Erwin Brandstetter
– Erwin Brandstetter, Commented May 19, 2017 at 14:21

Emilio Platzer · Accepted Answer · 2017-05-19 16:30:11Z

2

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)

answered May 19, 2017 at 16:30

Emilio Platzer

2,5191 gold badge23 silver badges33 bronze badges

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

1 Comment

V Damoy Over a year ago

Ah yes, string_agg... I'd heard about il, but not yet used. Thank you.

Erwin Brandstetter · Accepted Answer · 2017-05-19 14:44:48Z

1

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.

edited May 19, 2017 at 14:44

answered May 19, 2017 at 14:26

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

2 Comments

V Damoy Over a year ago

Thank you for your answer. Unfortunately my trigger was an "After trigger" (I had initially not published it). I'm going to simplify my code right now thanks to your advice, then I'll keep looking for my empty variables.

V Damoy Over a year ago

Thanks a lot. It was actually a "time" problem, but in my Python program. It filled the "operation_poly" table, then the trigger was launched and the other tables were filled. So, when the trigger was launched, other tables were still empty. Sorry for the noise. :-|

Collectives™ on Stack Overflow

Fill a variable with "array_to_string" in a plpgsql trigger function

2 Answers 2

using `array_agg`

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

using array_agg

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related

using `array_agg`