Postgres Pro
Downloads
Home   >   mailing lists

Thread: problem with function trigger

problem with function trigger

From
jclaudio@capitol.fr
Date:
<br /><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
/><fontface="sans-serif" size="2">Here's my  function code :</font><br /><br /><font face="sans-serif" size="2">CREATE
FUNCTIONpublic.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/><font face="sans-serif" size="2">single_price real; </font><br /><font face="sans-serif"
size="2">total_costreal; </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
/><fontface="sans-serif" size="2">BEGIN </font><br /><br /><font face="sans-serif" size="2">SELECT INTO id_line
id_line_tableFROM table WHERE id_line_table = NEW.id_line;</font><br /><font face="sans-serif" size="2">SELECT INTO
single_pricesingle_price_previ FROM table WHERE id_line_table = NEW.id_line;</font><br /><font face="sans-serif"
size="2">SELECTINTO total_cost total_cost_previ FROM table WHERE id_line_table = NEW.id_line;</font><br /><font
face="sans-serif"size="2">SELECT INTO quantity quantity_previ FROM table WHERE id_line_table = NEW.id_line;</font><br
/><fontface="sans-serif" size="2">SELECT INTO amort amortis FROM table WHERE id_line_table = NEW.id_line;</font><br
/><fontface="sans-serif" size="2">SELECT INTO month month_previ FROM table WHERE id_line_table = NEW.id_line;</font><br
/><fontface="sans-serif" size="2">SELECT INTO 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)
THENimpact:= ((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><br />

Re: problem with function trigger

From
Christoph Haller
Date:
> 
> Hi I'm trying to update a table column with a pl/pgsql function and a 
> trigger. 
> But I didn't managed to make it work so far.
> 
> Here's my  function code :
> 
> CREATE FUNCTION public.calcul_impact() RETURNS opaque AS '
> 
> DECLARE 
> id_line integer;
> quantity integer;
> single_price real; 
> total_cost real; 
> amort integer;
> month integer;
> impact real;
> 
> BEGIN 
> 
> SELECT INTO id_line id_line_table FROM table WHERE id_line_table = 
> NEW.id_line;
> SELECT INTO single_price single_price_previ FROM table WHERE id_line_table 
> = NEW.id_line;
> SELECT INTO total_cost total_cost_previ FROM table WHERE id_line_table = 
> NEW.id_line;
> SELECT INTO quantity quantity_previ FROM table WHERE id_line_table = 
> NEW.id_line;
> SELECT INTO amort amortis FROM table WHERE id_line_table = NEW.id_line;
> SELECT INTO month month_previ FROM table WHERE id_line_table = 
> NEW.id_line;
> SELECT INTO impact impact_previ FROM table WHERE id_line_table = 
> NEW.id_line;
> 
> IF(quantity IS NULL OR single_price IS NULL) THEN impact:= 0; 
> ELSE IF(quantity >= 12) THEN impact:= (total_cost / amort); 
>      ELSE IF(quantity < 12 AND single_price <= 500) THEN impact:= 
> total_cost; 
>           ELSE IF(quantity < 12 AND single_price > 500) THEN impact:= ((12 
> - month)*(total_cost/(amort*12))); 
>                END IF;
>           END IF;
>      END IF;
> END IF;
> 
> IF (TG_OP =''INSERT'' OR TG_OP=''UPDATE'') THEN 
>         UPDATE table SET impact_previ = impact WHERE id_line_table = 
> NEW.id_line; 
> END IF; 
> 
> RETURN NEW; 
> 
> END;
> 
> ' LANGUAGE 'plpgsql';
> 
> CREATE TRIGGER add_impact_previ BEFORE INSERT OR UPDATE ON public.budget 
> FOR EACH ROW EXECUTE PROCEDURE calcul_impact();
> 
> I always get the error :
> 
> Error SQL :
> ERROR:  record "new" has no field named "id_ligne"
> 
> Has anyone an idea about what's wrong ?
> 
> thanks for answering me

Could you please show the definition of table "budget". 
Not sure what you're trying to achieve by these SELECT INTO ... 
statements. 

Regards, Christoph