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

From Mike LAZLO
Subject ERROR: missing FROM-clause entry for table "new"
Date
Msg-id 625497565.102576.1556631694006@connect.xfinity.com
Whole thread Raw
Responses Re: ERROR: missing FROM-clause entry for table "new"  (Tom Lane <tgl@sss.pgh.pa.us>)
RE: ERROR: missing FROM-clause entry for table "new"  ("Voillequin, Jean-Marc" <Jean-Marc.Voillequin@moodys.com>)
List pgsql-sql

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

pgsql-sql by date:

Previous
From: Axel Rau
Date:
Subject: [RESOLVED] Re: Restrictions of channel arg of pg_notofy
Next
From: Tom Lane
Date:
Subject: Re: ERROR: missing FROM-clause entry for table "new"