#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    2
    Rep 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.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,375
    Rep Power
    391
    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?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    2
    Rep 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!

IMN logo majestic logo threadwatch logo seochat tools logo