problem with function trigger - Mailing list pgsql-sql

From jclaudio@capitol.fr
Subject problem with function trigger
Date
Msg-id OFA0F7ADE2.0E9D945D-ONC1256E1A.003C5030-C1256E1A.003C6D4D@capitol.fr
Whole thread Raw
List pgsql-sql
<br /><font face="sans-serif" size="2">Hi I'm trying to update a table column with a pl/pgsql function and a trigger.
</font><br/><font face="sans-serif" size="2">But I didn't managed to make it work so far.</font><br /><br /><font
face="sans-serif"size="2">Here's my  function code :</font><br /><br /><font face="sans-serif" size="2">CREATE FUNCTION
public.calcul_impact()RETURNS opaque AS '</font><br /><br /><font face="sans-serif" size="2">DECLARE </font><br /><font
face="sans-serif"size="2">id_line integer;</font><br /><font face="sans-serif" size="2">quantity integer;</font><br
/><fontface="sans-serif" size="2">single_price real; </font><br /><font face="sans-serif" size="2">total_cost real;
</font><br/><font face="sans-serif" size="2">amort integer;</font><br /><font face="sans-serif" size="2">month
integer;</font><br/><font face="sans-serif" size="2">impact real;</font><br /><br /><font face="sans-serif"
size="2">BEGIN</font><br /><br /><font face="sans-serif" size="2">SELECT INTO id_line id_line_table FROM table WHERE
id_line_table= NEW.id_line;</font><br /><font face="sans-serif" size="2">SELECT INTO single_price single_price_previ
FROMtable WHERE id_line_table = NEW.id_line;</font><br /><font face="sans-serif" size="2">SELECT INTO total_cost
total_cost_previFROM table WHERE id_line_table = NEW.id_line;</font><br /><font face="sans-serif" size="2">SELECT INTO
quantityquantity_previ FROM table WHERE id_line_table = NEW.id_line;</font><br /><font face="sans-serif"
size="2">SELECTINTO amort amortis FROM table WHERE id_line_table = NEW.id_line;</font><br /><font face="sans-serif"
size="2">SELECTINTO month month_previ FROM table WHERE id_line_table = NEW.id_line;</font><br /><font face="sans-serif"
size="2">SELECTINTO impact impact_previ FROM table WHERE id_line_table = NEW.id_line;</font><br /><br /><font
face="sans-serif"size="2">IF(quantity IS NULL OR single_price IS NULL) THEN impact:= 0; </font><br /><font
face="sans-serif"size="2">ELSE IF(quantity >= 12) THEN impact:= (total_cost / amort); </font><br /><font
face="sans-serif"size="2">     ELSE IF(quantity < 12 AND single_price <= 500) THEN impact:= total_cost;
</font><br/><font face="sans-serif" size="2">          ELSE IF(quantity < 12 AND single_price > 500) THEN
impact:=((12 - month)*(total_cost/(amort*12))); </font><br /><font face="sans-serif" size="2">               END
IF;</font><br/><font face="sans-serif" size="2">          END IF;</font><br /><font face="sans-serif" size="2">     END
IF;</font><br/><font face="sans-serif" size="2">END IF;</font><br /><br /><font face="sans-serif" size="2">IF (TG_OP
=''INSERT''OR TG_OP=''UPDATE'') THEN </font><br /><font face="sans-serif" size="2">        UPDATE table SET
impact_previ= impact WHERE id_line_table = NEW.id_line; </font><br /><font face="sans-serif" size="2">END IF;
</font><br/><br /><font face="sans-serif" size="2">RETURN NEW; </font><br /><br /><font face="sans-serif"
size="2">END;</font><br/><br /><font face="sans-serif" size="2">' LANGUAGE 'plpgsql';</font><br /><br /><font
face="sans-serif"size="2">CREATE TRIGGER add_impact_previ BEFORE INSERT OR UPDATE ON public.budget FOR EACH ROW EXECUTE
PROCEDUREcalcul_impact();</font><br /><br /><font face="sans-serif" size="2">I always get the error :</font><br /><br
/><fontface="sans-serif" size="2">Error SQL :</font><br /><font face="sans-serif" size="2">ERROR:  record "new" has no
fieldnamed "id_ligne"</font><br /><br /><font face="sans-serif" size="2">Has anyone an idea about what's wrong
?</font><br/><br /><font face="sans-serif" size="2">thanks for answering me</font> 

pgsql-sql by date:

Previous
From: Katarn
Date:
Subject: Unique field key or several fks ?
Next
From: "OizOne"
Date:
Subject: help with limiting query results