From: Torbjörn Andersson <tobbe@embryo.se>
To: pgsql-sql@postgresql.org
Cc: Tomas Eriksson <tomas@embryo.se>
Cc: Martin Eriksson <martin@embryo.se>
Cc: Magnus Hultin <hultin@embryo.se>
Cc: Archibald Zimonyi <arsi@cd.chalmers.se>
Cc: skystone@bigfoot.com
Subject: Nested loops and $13
Date: Wed, 09 Jan 2002 15:19:55 +0100
Message-ID: <B862101B.10A0%tobbe@embryo.se> (raw)
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';
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tobbe@embryo.se, tomas@embryo.se, martin@embryo.se, hultin@embryo.se, arsi@cd.chalmers.se, skystone@bigfoot.com
Subject: Re: Nested loops and $13
In-Reply-To: <B862101B.10A0%tobbe@embryo.se>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox