Received: from homer.websystems.co.za (unknown [196.25.70.70]) by postgresql.org (Postfix) with ESMTP id 8212B475872 for ; Fri, 8 Mar 2002 14:56:48 -0500 (EST) Received: from hano (bnb53-01-p150.nt.saix.net [155.239.180.150]) by homer.websystems.co.za (8.11.0/8.11.0) with SMTP id g28K1xI26513; Fri, 8 Mar 2002 22:02:00 +0200 Message-ID: <000201c1c6db$51da8f20$c80ba8c0@sabex.com> From: "PG Explorer" To: "Ismail Bouabdallah" , References: <3C83BDC1.9E181438@cs.man.ac.uk> Subject: Re: PL/pgsql Date: Fri, 8 Mar 2002 21:54:22 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200203/103 X-Sequence-Number: 6984 You have a syntax typo IF NEW.SITE_REFERNCE = SITE_REFERENCE IF NEW.SITE_REFERENCE = SITE_REFERENCE http://www.pgexplorer.com PostgreSQL GUI Tool ----- Original Message ----- From: "Ismail Bouabdallah" To: Sent: Monday, March 04, 2002 8:32 PM Subject: [SQL] PL/pgsql > Hi can any one help me out with the following trigger that I have > written.... > > > CREATE FUNCTION new_site() RETURNS OPAQUE AS ' > BEGIN > IF NEW.SITE_REFERNCE = SITE_REFERENCE > THEN > SITE.END_DATE := NEW.START_DATE > SITE.CURRENCY_FLAG := 2 > END; > ' LANGUAGE 'plpgsql'; > > CREATE TRIGGER NEWSITE > AFTER INSERT ON SITE > FOR EACH ROW > EXECUTE PROCEDURE new_site(); > > the trigger enforces versioning of new sites inserted into the site > table of my land_use database.... > but after defining the function I can no longer insert new sites into > the database. > > i receive the following error from psql: > > ERROR: fmgr_info: function 56664: cache lookup failed > > when I try to insert the following data: > > INSERT INTO SITE > (SITE_REFERENCE,UPRN,LA_REFERENCE,VERSION_NO,CURRENCY_FLAG,START_DATE,END_DA TE,EASTING,NORTHING,PAO_DESCRIPTION,STREET_DESCRIPTION,LOCALITY_DESCRIPTION, TOWN_DESCRIPTION,POSTCODE,LA_CODE,AREA,LAND_TYPE_REF,LAND_USE_DIVISION_REF,L AND_USE_CLASS_REF,LAND_USE_SUB_CLASS_REF,DERELICTION_REF,OWNER_REF,PLANNING_ STATUS_REF,PROPOSED_USE_REF,HOUSING_COMPONENT,HOUSING_SUIT_REF,MOST_SUITABLE _USE_REF,EST_HOUSING_CAPACITY,HOUSING_DENSITY,LEGAL_CONSTRAINTS,PHYSICAL_CON STRAINTS,AVAILABLE_ON_MARKET,AGENT_NAME,EP_RDA_INVOLVEMENT,GENERAL_SITE_INFO RMATION,BOUNDARY_ID) > > VALUES (345600001,null,null,2,1,'20020311',null,414722,130160,'57 TO > 61','GREENCROFT STREET',null,'SNAILSBURY','SN3 > 5BX',3456,0.062,2,11,44,133,11,3,4,2,4,1,2,10,161,1,2,3,null,2,null,34560000 11); > > > ---------------------------------------------------------------------------- ---- > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >