Error while doing insert, something happens with my triggered function, please help

Dev Shed Forums Sponsor:

November 23rd, 2012, 08:02 AM

negroscuro

Registered User

Join Date: Nov 2012

Posts: 2

Time spent in forums: 18 m 35 sec
Reputation Power: 0

Error while doing insert, something happens with my triggered function, please help

This is my table:

Code:

CREATE TABLE location
(
  id serial NOT NULL,
  district character varying(100),
  county character varying(100),
  subcounty character varying(100),
  village character varying(100),
  gid integer,
  CONSTRAINT location_pkey PRIMARY KEY (id )
)

But I also have this other table:

Code:

CREATE TABLE villages
(
  gid serial NOT NULL,
  dname character varying(32),
  cname character varying(32),
  sname character varying(32),
  pname character varying(32),
  vname character varying(32),
  the_geom geometry,
  the_geom2 geometry,
  CONSTRAINT villages_pkey PRIMARY KEY (gid ),
)

This is my function and trigger:

Code:

CREATE OR REPLACE FUNCTION getvillagegidfunction() RETURNS TRIGGER AS $$
  DECLARE
  register location;
  BEGIN
  
   SELECT * INTO register 
   FROM villages v
   WHERE NEW.village::TEXT=v.vname
   AND NEW.subcounty::TEXT=v.sname
   AND NEW.county::TEXT=v.cname
   AND NEW.district::TEXT=v.dname;
   
   new.gid := register.gid;

   RETURN NEW;
  END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER getvillagegidtrigger BEFORE INSERT
ON location FOR EACH ROW EXECUTE PROCEDURE getvillagegidfunction();

And this is my insert:

Code:

INSERT INTO location 
(id,district,county,subcounty,village, gid)
VALUES (2333,'KASESE', 'KASESE MUNICIPALITY', 'NYAMWAMBA DIVISION', 'SEBWE',  113);

So here is my error:

Code:

ERROR:  invalid input syntax for integer: "SEBWE"
CONTEXT:  PL/pgSQL function "getvillagegidfunction" line 5 at SQL statement

********** Error **********

ERROR: invalid input syntax for integer: "SEBWE"
SQL state: 22P02
Context: PL/pgSQL function "getvillagegidfunction" line 5 at SQL statement

The aim of this function is to retrieve the GID from villages table and add it to the gid column when any new row is inserted in location table. I do this because locations have the same fields as a village, so I serach with a select for the village matching the new location.

I have no idea about what I am doing wrong, so any help will be much appreciated.

November 23rd, 2012, 08:29 AM

swampBoogie

Contributing User

Join Date: Jan 2003

Location: Paris Uppland

Posts: 2,351

Time spent in forums: 1 Month 4 Days 8 h 2 m 22 sec
Reputation Power: 390

Code:

CREATE OR REPLACE FUNCTION getvillagegidfunction() RETURNS TRIGGER AS $$
  DECLARE
  register location;
  BEGIN

shouldn't that be

Code:

CREATE OR REPLACE FUNCTION getvillagegidfunction() RETURNS TRIGGER AS $$
  DECLARE
  register villages;
  BEGIN

instead?

November 23rd, 2012, 08:30 AM

negroscuro

Registered User

Join Date: Nov 2012

Posts: 2

Time spent in forums: 18 m 35 sec
Reputation Power: 0

I think it maybe related to the recent update I did to the table location.
I added the last column 'GID'... I dont know if it may affect...

--edit---

Yes, thank you for your reply, I didnt realice, but thats it, now works. Simple things usually are the worst to find out.
Thank you!

Error while doing insert, something happens with my triggered function, please help

Developer Shed Advertisers and Affiliates