postgresql - trigger function with condition

Question

I'm trying to create a trigger with a condition. Based on the geom length I want the attribute "nom" (= name) to be written in upper case or lower case.

here's what I have:

CREATE OR REPLACE FUNCTION public.test_upper_lower()  
RETURNS trigger AS  
    $BODY$
        BEGIN  
            NEW.dummy:= (ST_Length(new.geom)); 
            if (SELECT dummy FROM ligne_ligne)>100 
            then NEW.nom:= LOWER(nom) FROM ligne_ligne; 
            else NEW.nom:= UPPER(nom) FROM ligne_ligne;  
            end if;
            RETURN NEW;
        END;
    $BODY$
LANGUAGE plpgsql;  
DROP trigger IF EXISTS test_upper_lower on public.ligne_ligne;  
CREATE trigger test_upper_lower BEFORE INSERT OR UPDATE on public.ligne_ligne  
    FOR EACH ROW 
    EXECUTE PROCEDURE public.test_upper_lower();

With this I have a "more than one row returned by a subquery" error

Based on other questions on this forum I tried it using case instead of if and using when in the trigger itself not the function but neither are working

Any ideas ? Thanks

user330315 · Accepted Answer · 2020-01-15 08:17:27Z

1

You don't need (or can actually) use SELECT statements to access data from the inserted row.

The part SELECT dummy FROM ligne_ligne returns all rows from that table - not just from the one relevant to the trigger.

As you just want to check the value you just calculated, simply use new.dummy at that point:

CREATE OR REPLACE FUNCTION public.test_upper_lower()  
RETURNS trigger AS  
$BODY$
BEGIN  
  NEW.dummy:= ST_Length(new.geom); 
  if new.dummy > 100 then --<< no SELECT necessary
    NEW.nom:= LOWER(new.nom); --<< no "FROM", just access the value 
  else 
    NEW.nom:= UPPER(new.nom);  
  end if;
  RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

answered Jan 15, 2020 at 8:17

user330315

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

Collectives™ on Stack Overflow

postgresql - trigger function with condition

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related