Обсуждение: 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_DATE,EASTING,NORTHING,PAO_DESCRIPTION,STREET_DESCRIPTION,LOCALITY_DESCRIPTION,TOWN_DESCRIPTION,POSTCODE,LA_CODE,AREA,LAND_TYPE_REF,LAND_USE_DIVISION_REF,LAND_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_CONSTRAINTS,AVAILABLE_ON_MARKET,AGENT_NAME,EP_RDA_INVOLVEMENT,GENERAL_SITE_INFORMATION,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,3456000011);

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" <bouabdi9@cs.man.ac.uk>
To: <pgsql-sql@postgresql.org>
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
>

> You have a syntax typo
> IF NEW.SITE_REFERNCE = SITE_REFERENCE
>
> IF NEW.SITE_REFERENCE = SITE_REFERENCE
PROBABLY
> ----- Original Message -----
> From: "Ismail Bouabdallah" <bouabdi9@cs.man.ac.uk>
> To: <pgsql-sql@postgresql.org>
> Sent: Monday, March 04, 2002 8:32 PM
> Subject: [SQL] PL/pgsql
>

> > ERROR:  fmgr_info: function 56664: cache lookup failed
> >
But this message I've got when I've not deleted trigger on table. Try to
delete and create trigger function and trigger again.

GOOD LUCK!
Dinar