How multiple rows can be selected from a table in triggers in postgres

Question

I am trying to write a trigger in which I have to create a Json after getting data(multiple rows) from another table. I have two table task and data. data table contains multiple information about the task in key/value pair.

 task schema
 taskid
 description 
 value

data table schema

  id
  taskid
  key
  value

Now in trigger I want to fetch all data corresponding to a task and prepare a JSON from that. I am using below

 CREATE OR REPLACE FUNCTION event()
  RETURNS trigger AS
     $BODY$

        DECLARE
         _message json; 
         _data data;

         BEGIN

         SELECT * INTO _data FROM data WHERE data.taskid = New.taskid;
          IF      TG_OP = 'INSERT' THEN
                          _messag:=json_build_object('taskId',NEW.taskid,'description',NEW.description,'value',NEW.value,'clientis',_data);

          END IF;


       INSERT INTO events(message)
       VALUES(_messag);

       RETURN NULL;
       END;

        $BODY$
LANGUAGE plpgsql VOLATILE

Here, I am using below query to get all data from data table corresponding to a taskid

SELECT * INTO _data FROM data WHERE data.taskid = New.taskid;

Here, the problem is that I am getting only one row. How can all rows will be fetched? If anyone has any idea?

I have to get all the data from the table corresponding to a task. I tried jsonb_agg(to_jsonb(data)) ; but it is not working. — shruti garg
– shruti garg, Commented Apr 14, 2020 at 12:33

user330315 · Accepted Answer · 2020-04-14 11:43:48Z

0

You can aggregate everything into a single JSON value:

declare
...
  _data jsonb; --<< change the data type to jsonb
begin
...
IF TG_OP = 'INSERT' THEN
  -- only select everything if you really need it
  SELECT jsonb_agg(to_jsonb(data)) 
    INTO _data 
  FROM data 
  WHERE data.taskid = New.taskid;

  _message := json_build_object('taskId',NEW.taskid,'description',NEW.description,'value',NEW.value,'clientis',_data);
END IF;
...

answered Apr 14, 2020 at 11:43

user330315

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

Comments

shruti garg · Accepted Answer · 2020-04-14 12:56:57Z

0

It works for me:

CREATE OR REPLACE FUNCTION event()
 RETURNS trigger AS
 $BODY$

    DECLARE
    _message json; 
    _data jsonb;

     BEGIN

   *****SELECT json_agg(tmp)  
    INTO _data
  FROM (
    -- Your subquery goes here, for example:
    SELECT data.key, data.value
    FROM.data
    WHERE data.taskid = New.taskid
    ) tmp;*****

      IF      TG_OP = 'INSERT' THEN
                      _messag:=json_build_object('taskId',NEW.taskid,'description',NEW.description,'value',NEW.value,'clientis',_data);

      END IF;

   INSERT INTO events(message)
   VALUES(_messag);

   RETURN NULL;
   END;

    $BODY$
  LANGUAGE plpgsql VOLATILE

Research link: select statement in postgres function called inside a trigger

edited Apr 14, 2020 at 12:56

answered Apr 14, 2020 at 12:51

shruti garg

3471 gold badge2 silver badges15 bronze badges

Collectives™ on Stack Overflow

How multiple rows can be selected from a table in triggers in postgres

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related