From: Christoph Haller <ch@rodos.fzk.de>
To: jclaudio@capitol.fr
Cc: pgsql-sql@postgresql.org
Subject: Re: problem with function trigger
Date: Fri, 16 Jan 2004 11:34:44 MET
Message-ID: <200401161034.LAA18056@rodos> (raw)
In-Reply-To: <OF2ACB9FBF.6F44ED00-ONC1256E1A.004B487C-C1256E1A.004B5022@capitol.fr>
>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, jclaudio@capitol.fr
Subject: Re: problem with function trigger
In-Reply-To: <200401161034.LAA18056@rodos>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox