pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Nested 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