Re: problem with function trigger

From: Christoph Haller <ch(at)rodos(dot)fzk(dot)de>
To: jclaudio(at)capitol(dot)fr
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: problem with function trigger
Date: 2004-01-16 11:34:44
Message-ID: 200401161034.LAA18056@rodos
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

>
> 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

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Michael Glaesemann 2004-01-16 11:44:15 Re: sql query with join and parameter in postgresql function
Previous Message Philippe Lang 2004-01-16 11:30:56 sql query with join and parameter in postgresql function