X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 6FAA5D1B47D for ; Fri, 16 Jan 2004 10:35:23 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 03448-07 for ; Fri, 16 Jan 2004 06:34:54 -0400 (AST) Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52]) by svr1.postgresql.org (Postfix) with SMTP id 31590D1DB75 for ; Fri, 16 Jan 2004 06:34:50 -0400 (AST) Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ; Fri Jan 16 11:34:46 2004 +0100 Received: œby rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id LAA18541; Fri, 16 Jan 2004 11:34:45 +0100 (MET) From: Christoph Haller Message-Id: <200401161034.LAA18056@rodos> Subject: Re: problem with function trigger To: jclaudio@capitol.fr Date: Fri, 16 Jan 2004 11:34:44 MET Cc: pgsql-sql@postgresql.org In-Reply-To: ; from "jclaudio@capitol.fr" at Jan 13, 104 2:44 pm X-Mailer: Elm [revision: 212.4] X-Virus-Scanned: by amavisd-new at postgresql.org X-Amavis-Alert: BAD HEADER Non-encoded 8-bit data (char 9C hex) in message header 'Received' Received: \234by rodos.fzk.de... ^ X-Archive-Number: 200401/136 X-Sequence-Number: 16529 > > 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