Received: from multivac.embryo.se (multivac.embryo.se [195.149.181.2]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g09G1kB17962 for ; Wed, 9 Jan 2002 11:01:46 -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 16OLB6-0003Nv-00 for ; Wed, 09 Jan 2002 17:02:12 +0100 User-Agent: Microsoft-Entourage/10.0.0.1309 Date: Wed, 09 Jan 2002 17:02:08 +0100 Subject: Re: Nested loops and $13 From: Torbj=?ISO-8859-1?B?9g==?=rn Andersson To: Message-ID: In-Reply-To: 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 g09G4eg18866 X-Archive-Number: 200201/65 X-Sequence-Number: 6155 Hi again, Found the error. It was a typo in this line: row_air.info, v_person. v_company); Should be v_person, v_company. Strange error message though. Anyways, after correcting I get this: safari=# SELECT func_air_fare_copy(31,76,'20021010','20011212',2,2); ERROR: Attribute 's_air_fare_id' is of type 'int4' but expression is of type 'bpchar' You will need to rewrite or cast the expression I use s_air_fare_id on two occasions. Once in the SELECT that creates the recordset for the outer loop. Here it is the primary key of s_air_fare. Then in the WHERE clause for the recordset of the inner loop where it is the foreign key from s_air_fare_s_airline_rt referencing s_air_fare. In neither case should there be any need for casting. The record row_air has the datatypes of the original table and when I later use it in the WHERE clause I compare two integers. Probably I'm missing something here..... 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); SELECT INTO v_air_fare 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 afart WHERE afart.s_air_fare_id = row_air.s_air_fare_id LOOP -- insert s_air_fare_s_airline_rt INSERT INTO s_air_fare_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';