Received: from multivac.embryo.se (multivac.embryo.se [195.149.181.2]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g09EJYB01640 for ; Wed, 9 Jan 2002 09:19:35 -0500 (EST) (envelope-from tobbe@embryo.se) Received: from tuba.embryo.se ([172.16.0.67]) by multivac.embryo.se with esmtp (Exim 3.12 #1 (Debian)) id 16OJaA-0003Co-00; Wed, 09 Jan 2002 15:19:58 +0100 User-Agent: Microsoft-Entourage/10.0.0.1309 Date: Wed, 09 Jan 2002 15:19:55 +0100 Subject: Nested loops and $13 From: Torbj=?ISO-8859-1?B?9g==?=rn Andersson To: CC: Tomas Eriksson , Martin Eriksson , Magnus Hultin , Archibald Zimonyi , Message-ID: Mime-version: 1.0 Content-type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by postgresql.org id g09EObC02001 X-Archive-Number: 200201/62 X-Sequence-Number: 6153 Hi, I'm trying to write a function that duplicates information from two tables and assigns them to a new contract. The relations are s_air_contract has many s_air_fare which has many s_air_fare_s_airline_rt. A nested loop seemed like a straightforward way to do it. First insert a new air fare and then insert the dependant airlines. The code below generates this error message: safari=# SELECT func_air_fare_copy(31,76,'20021010','20011212',2,2); ERROR: parser: parse error at or near "$13" I only use 6 variables so $13 has me stumped, ideas anyone?? Best regards, Torbjörn Andersson --------------------------------------------------- Embryo Communication phone: +46 (0) 31-774 39 11(00) Kungsgatan 7a fax: +46 (0)31 774 07 80 S-411 19 Göteborg mobile: 0708-30 70 04 Sweden home: http://www.embryo.se/ mail: torbjorn.andersson@embryo.se --------------------------------------------------- --Code-------------- DROP FUNCTION func_air_fare_copy(int, int, date, date, int, int); CREATE FUNCTION func_air_fare_copy(int, int, date, date, int, int) RETURNS int AS' DECLARE v_contract_old ALIAS FOR $1; v_contract_new ALIAS FOR $2; v_from ALIAS FOR $3; v_to ALIAS FOR $4; v_person ALIAS FOR $5; v_company ALIAS FOR $6; v_number int := 0; v_air_fare int :=0; row_air RECORD; row_airline RECORD; BEGIN -- Insert one row into s_air_fare, get new id, insert airline and code. FOR row_air IN SELECT s_air_fare_id, dep__s_airport_code, dest__s_airport_code, net, gross, gross_infant, gross_child, locked, info FROM s_air_fare WHERE s_air_contract_id = v_contract_old LOOP -- insert air_fare INSERT INTO s_air_fare (s_air_contract_id, dep__s_airport_code, dest__s_airport_code, dep_from, dep_to, net, gross, gross_infant, gross_child, locked, info, a_person_id, a_company_id) VALUES (v_contract_new, row_air.dep__s_airport_code, row_air.dest__s_airport_code, v_from, v_to, row_air.net, row_air.gross, row_air.gross_infant, row_air.gross_child, row_air.locked, row_air.info, v_person. v_company); v_air_fare = (SELECT last_value FROM s_air_fare_s_air_fare_id_seq); -- airlines and classes for the current row FOR row_airline IN SELECT s_airline_code, s_class FROM s_air_fare_s_airline_rt WHERE s_air_fare_id = row_air.s_air_fare_id LOOP -- insert s_air_fare_s_airline_rt INSERT INTO s_airfare_s_airline_rt VALUES(v_air_fare, row_airline.s_airline_code, row_airline.s_class); END LOOP; v_number := v_number + 1; END LOOP; RETURN v_number; END; ' LANGUAGE 'plpgsql';