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




view thread (4+ messages)  latest in thread

Message-ID: <B862101B.10A0%tobbe@embryo.se>
Permalink:  ../B862101B.10A0%25tobbe@embryo.se/
Also on:    postgresql.org/message-id/B862101B.10A0%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, 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