Thread: Nested loops and $13
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 ALIASFOR $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_airlineIN SELECT s_airline_code, s_class FROM s_air_fare_s_airline_rt WHEREs_air_fare_id = row_air.s_air_fare_id LOOP -- insert s_air_fare_s_airline_rt INSERTINTO 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';
Torbjörn Andersson <tobbe@embryo.se> writes:
> 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??
The $13 is probably being inserted by plpgsql into one of the queries
that it feeds down to the main SQL parser, as a placeholder for one of
your plpgsql variables. What this is telling you is that there's a
syntax error in one of the SQL queries, but unfortunately it's not
telling you much about exactly where. Easiest way to isolate the
problem is to turn on query logging (set debug_print_query = true)
before you run the function for the first time; then look in the
postmaster log to see the passed-down queries.
BTW, I believe that 7.2 will provide a line number in the plpgsql
function in this sort of situation, which should help some.
regards, tom lane
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 ALIASFOR $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_farelast_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_rtVALUES(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';
> Easiest way to isolate the > problem is to turn on query logging (set debug_print_query = true) > before you run the function for the first time; then look in the > postmaster log to see the passed-down queries. Thanks Tom, I didn't know about that one. And it helped me to find two other errors as well.... 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 --------------------------------------------------- "Att idag tänka annorlunda än igår skiljer den vise från den envise." John Steinbeck