Re: Nested loops and $13

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(dot)andersson(at)embryo(dot)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';