pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Torbjörn Andersson <tobbe@embryo.se>
To: pgsql-sql@postgresql.org
Subject: Re: Nested loops and $13
Date: Wed, 09 Jan 2002 17:02:08 +0100
Message-ID: <B8622810.10B2%tobbe@embryo.se> (raw)
In-Reply-To: <B862101B.10A0%tobbe@embryo.se>

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';





view thread (4+ messages)  latest in thread

Message-ID: <B8622810.10B2%tobbe@embryo.se>
Permalink:  ../B8622810.10B2%25tobbe@embryo.se/
Also on:    postgresql.org/message-id/B8622810.10B2%tobbe@embryo.se

 · 

reply

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
  Subject: Re: Nested loops and $13
  In-Reply-To: <B8622810.10B2%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