Postgres set final value based on some condition

Question

My Postgres Table has a column called status which consists of complete, failed or running. Based on these value I want to set the final result if all completed then final result = completed if one failed then result = failed if one running and no failed entry then the result is running.

code | status   | parent_code
1    | complete |      3
2    | running  |      3
3    | ----     |

In the above table, for code = 3 I want to set the status to running.

Also if someone can tell me how to set the trigger for the same, whenever changes happen I want to keep the value of parent updated.

So your table will always have 2 records per process ? how parent_code is decided ? — Digvijay S
– Digvijay S, Commented Mar 17, 2020 at 6:38

user330315 · Accepted Answer · 2020-03-17 06:34:39Z

2

I would use a sub-query using filtered aggregation to count the total number and the number of failed, running and completed rows and use that for an UPDATE statement:

update the_table tt
   set status = case 
                   when x.failed > 0 then 'failed'
                   when x.running > 0 then 'running'
                   when x.total_rows = x.completed then 'completed'
                end
from (
  select parent_code, 
         count(*) as total_rows,
         count(*) filter (where status = 'running') as running,
         count(*) filter (where status = 'failed') as failed,
         count(*) filter (where status = 'completed') as completed
  from the_table
  where parent_code is not null
  group by parent_code
) x 
where tt.parent_code is null   
  and tt.code = x.parent_code

answered Mar 17, 2020 at 6:34

user330315

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

Comments

Gordon Linoff · Accepted Answer · 2020-03-17 11:55:33Z

1

A horse with no names answer is just fine (and I've upvoted it). This is a suggestion on a simplification:

update the_table tt
    set status = p.status
    from (select parent_code, 
                 coalesce(max(status) filter (where status = 'failed'),
                          max(status) filter (where status = 'running'),
                          max(status) filter (where status = 'completed')
                         )
           from the_table
           group by parent_code
          ) p 
    where tt.parent_code is null and 
          tt.code = p.parent_code;

answered Mar 17, 2020 at 11:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Ajinkya Dhote · Accepted Answer · 2020-03-17 15:43:06Z

0

Both the above answer given by @a_horse_with_no_name and @Gordon Linoff is correct. Here I have converted it to stored procedure and written a trigger for the same. It might be helpful for someone.

Writing a Stored Procedure.
Creating a trigger.

Writing a Stored Procedure.

CREATE OR REPLACE FUNCTION UpdateStatus()
RETURNS TRIGGER AS $$
BEGIN
update the_table tt
   set status = case 
                   when x.failed > 0 then 'failed'
                   when x.running > 0 then 'running'
                   when x.total_rows = x.completed then 'completed'
                end
from (
  select parent_code, 
         count(*) as total_rows,
         count(*) filter (where device_discovery_status = 'running') as running,
         count(*) filter (where device_discovery_status = 'failed') as failed,
         count(*) filter (where device_discovery_status = 'completed') as completed
  from the_table
  where parent_code is not null
  group by parent_code
) x 
where tt.parent_code is null   
  and tt.code = x.parent_code;
RETURN NULL;
END;
$$ 
LANGUAGE plpgsql;

Creating a trigger.

CREATE TRIGGER update_status_on_change
    AFTER UPDATE ON the_table
    FOR EACH ROW
    WHEN (OLD.* IS DISTINCT FROM NEW.*)
    EXECUTE PROCEDURE UpdateStatus();

So whenever a status gets changed update_status_on_change trigger will be triggered and it will set the final status accordingly.

answered Mar 17, 2020 at 15:43

Ajinkya Dhote

1,4883 gold badges14 silver badges26 bronze badges

2 Comments

user330315 Over a year ago

I would make that a statement level trigger, so it's only fired once even if you insert or update thousands of rows

Ajinkya Dhote Over a year ago

Thanks for the suggestion @a_horse_with_no_name. I will explore the statement-level trigger.

Collectives™ on Stack Overflow

Postgres set final value based on some condition

3 Answers 3

Comments

Comments

Writing a Stored Procedure.

Creating a trigger.

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Writing a Stored Procedure.

Creating a trigger.

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related