RE: ERROR: missing FROM-clause entry for table "new" - Mailing list pgsql-sql

Hello,

You wrote NEW twice: NEW.NEW

INSERT INTO AIRCRAFT
VALUES (NEW.ev_id
, NEW.NEW.aircraft_key::int
, NEW.NEW.regis_no
, NEW.NEW.ntsb_no

CAUTION: This email originated from outside of Moody's. Do not click links or open attachments unless you recognize the sender and know the content is safe.

What is wrong with this picture:

--trigger function:

CREATE OR REPLACE FUNCTION populateAircraft()
RETURNS TRIGGER AS $populateAircraft$
BEGIN


INSERT INTO AIRCRAFT
VALUES (NEW.ev_id
, NEW.NEW.aircraft_key::int
, NEW.NEW.regis_no
, NEW.NEW.ntsb_no
, NEW.acft_missing
, NEW.far_part
, NEW.flt_plan_filed
, NEW.flight_plan_activated
, NEW.damage
, NEW.acft_fire
, NEW.acft_expl
, NEW.acft_make
, NEW.acft_model
, NEW.acft_series
, NEW.acft_serial_no
, NEW.cert_max_gr_wt::int
, NEW.acft_category
, NEW.acft_reg_cls
, NEW.homebuilt
, NEW.fc_seats::int
, NEW.cc_seats::int
, NEW.pax_seats::int
, NEW.total_seats::smallint
, NEW.num_eng::smallint
, NEW.fixed_retractable
, NEW.type_last_insp
, TO_TIMESTAMP(NEW.date_last_insp,'MM/DD/YY HH24:MI:SS')
, NEW.afm_hrs_last_insp::real
, NEW.afm_hrs::real
, NEW.elt_install
, NEW.elt_oper
, NEW.elt_aided_loc_ev
, NEW.elt_type
, NEW.owner_acft
, NEW.owner_street
, NEW.owner_city
, NEW.owner_state
, NEW.owner_country
, NEW.owner_zip
, NEW.oper_individual_name
, NEW.oper_name
, NEW.oper_same
, NEW.oper_dba
, NEW.oper_addr_same
, NEW.oper_street
, NEW.oper_city
, NEW.oper_state
, NEW.oper_country
, NEW.oper_zip
, NEW.oper_code
, NEW.certs_held
, NEW.oprtng_cert
, NEW.oper_cert
, NEW.oper_cert_num
, NEW.oper_sched
, NEW.oper_dom_int
, NEW.oper_pax_cargo
, NEW.type_fly
, NEW.second_pilot
, NEW.dprt_pt_same_ev
, NEW.dprt_apt_id
, NEW.dprt_city
, NEW.dprt_state
, NEW.dprt_country
, NEW.dprt_time::smallint
, NEW.dprt_timezn
, NEW.dest_same_local
, NEW.dest_apt_id
, NEW.dest_city
, NEW.dest_state
, NEW.dest_country
, NEW.phase_flt_spec::int
, NEW.report_to_icao
, NEW.evacuation
, TO_TIMESTAMP(NEW.lchg_date,'MM/DD/YY HH24:MI:SS')
, NEW.lchg_userid
, NEW.afm_hrs_since
, NEW.rwy_num
, NEW.rwy_len::int
, NEW.rwy_width::int
, NEW.site_seeing
, NEW.air_medical
, NEW.med_type_flight
, NEW.acft_year::int
, NEW.fuel_on_board
, NEW.commercial_space_flight::bit
, NEW.unmanned::bit
, NEW.ifr_equipped_cert::bit
, NEW.elt_mounted_aircraft::bit
, NEW.elt_connected_antenna::bit
, NEW.elt_manufacturer
, NEW.elt_model
, NEW.elt_reason_other::text);
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$populateAircraft$ LANGUAGE plpgsql;

-- Trigger:

CREATE TRIGGER UTR_AI_STAGE_AIRCRAFT
AFTER INSERT ON STAGE_AIRCRAFT
FOR EACH ROW
EXECUTE PROCEDURE populateAircraft();

--Insert:

INSERT INTO ntsbdata.ssa.stage_aircraft (ev_id, aircraft_key, regis_no, ntsb_no, acft_missing, far_part, flt_plan_filed, flight_plan_activated, damage, acft_fire, acft_expl, acft_make, acft_model, acft_series, acft_serial_no, cert_max_gr_wt, acft_category, acft_reg_cls, homebuilt, fc_seats, cc_seats, pax_seats, total_seats, num_eng, fixed_retractable, type_last_insp, date_last_insp, afm_hrs_last_insp, afm_hrs, elt_install, elt_oper, elt_aided_loc_ev, elt_type, owner_acft, owner_street, owner_city, owner_state, owner_country, owner_zip, oper_individual_name, oper_name, oper_same, oper_dba, oper_addr_same, oper_street, oper_city, oper_state, oper_country, oper_zip, oper_code, certs_held, oprtng_cert, oper_cert, oper_cert_num, oper_sched, oper_dom_int, oper_pax_cargo, type_fly, second_pilot, dprt_pt_same_ev, dprt_apt_id, dprt_city, dprt_state, dprt_country, dprt_time, dprt_timezn, dest_same_local, dest_apt_id, dest_city, dest_state, dest_country, phase_flt_spec, report_to_icao, evacuation, lchg_date, lchg_userid, afm_hrs_since, rwy_num, rwy_len, rwy_width, site_seeing, air_medical, med_type_flight, acft_year, fuel_on_board, commercial_space_flight, unmanned, ifr_equipped_cert, elt_mounted_aircraft, elt_connected_antenna, elt_manufacturer, elt_model, elt_reason_other) VALUES ('20001208X07734','1','N6172C','LAX97FA143 ','N','091 ','IFR ',NULL,'DEST','NONE','NONE','Cessna ','T210N ','T210N ','210-63820 ','3800','AIR ','USUS','N',NULL,NULL,NULL,'6','1','RETR','ANNL','11/16/96 00:00:00','3.40000000e+01','1.64300000e+03','Y','U','U',NULL,'JAMES E. ELDREDGE ','4039 KNOLL RIDGE AVE. ','LAS VEGAS ','NV',' ','89030 ',NULL,' ','Y','RANGER ENTERPRISES ','Y',' ',' ',' ',' ',' ',' ','N','N ','UNK',NULL,NULL,NULL,NULL,'PERS','N','N','LVS ','LAS VEGAS ','NM',' ','1750','MST','LOCL','VGT ',' ','NV',' ','540',NULL,NULL,'01/02/01 10:34:39','dbo',NULL,'0',NULL,NULL,'N','N',NULL,NULL,NULL,'0','0','0','0','0',NULL,NULL,NULL) was aborted: ERROR: missing FROM-clause entry for table "new"

Where: PL/pgSQL function populateaircraft() line 26 at SQL statement

?

The only thing I can find related to this error with NEW variables is dynamic pl/pgsql which this is not.

Thanks in advanced