pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNested loops and $13
4+ messages / 2 participants
[nested] [flat]
* Nested loops and $13
@ 2002-01-09 14:19 Torbjörn Andersson <tobbe@embryo.se>
2002-01-09 15:37 ` Re: Nested loops and $13 Tom Lane <tgl@sss.pgh.pa.us>
2002-01-09 16:02 ` Re: Nested loops and $13 Torbjörn Andersson <tobbe@embryo.se>
0 siblings, 2 replies; 4+ messages in thread
From: Torbjörn Andersson @ 2002-01-09 14:19 UTC (permalink / raw)
To: pgsql-sql; +Cc: Tomas Eriksson <tomas@embryo.se>; Martin Eriksson <martin@embryo.se>; Magnus Hultin <hultin@embryo.se>; Archibald Zimonyi <arsi@cd.chalmers.se>; skystone@bigfoot.com
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';
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Nested loops and $13
2002-01-09 14:19 Nested loops and $13 Torbjörn Andersson <tobbe@embryo.se>
@ 2002-01-09 15:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-01-09 16:41 ` Re: Nested loops and $13 Torbjörn Andersson <tobbe@embryo.se>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2002-01-09 15:37 UTC (permalink / raw)
To: Torbjörn Andersson <tobbe@embryo.se>; +Cc: pgsql-sql@postgresql.org, Tomas Eriksson <tomas@embryo.se>; Martin Eriksson <martin@embryo.se>; Magnus Hultin <hultin@embryo.se>; Archibald Zimonyi <arsi@cd.chalmers.se>; skystone@bigfoot.com
Torbj=?ISO-8859-1?B?9g==?=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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Nested loops and $13
2002-01-09 14:19 Nested loops and $13 Torbjörn Andersson <tobbe@embryo.se>
2002-01-09 15:37 ` Re: Nested loops and $13 Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-01-09 16:41 ` Torbjörn Andersson <tobbe@embryo.se>
0 siblings, 0 replies; 4+ messages in thread
From: Torbjörn Andersson @ 2002-01-09 16:41 UTC (permalink / raw)
To: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Nested loops and $13
2002-01-09 14:19 Nested loops and $13 Torbjörn Andersson <tobbe@embryo.se>
@ 2002-01-09 16:02 ` Torbjörn Andersson <tobbe@embryo.se>
1 sibling, 0 replies; 4+ messages in thread
From: Torbjörn Andersson @ 2002-01-09 16:02 UTC (permalink / raw)
To: pgsql-sql
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';
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-01-09 16:41 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-01-09 14:19 Nested loops and $13 Torbjörn Andersson <tobbe@embryo.se>
2002-01-09 15:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-01-09 16:41 ` Torbjörn Andersson <tobbe@embryo.se>
2002-01-09 16:02 ` Torbjörn Andersson <tobbe@embryo.se>
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