postgresql 8.4 trigger/storedproc error : how to fix it ?

Question

Hi there,

An INSERT on a table triggers a stored proc where the following error occurs.

ERROR: column "targetedfamily" is of type boolean but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Where: PL/pgSQL function "fn_family_audit" line 19 at SQL statement

And here's the ERRING stored proc (notice that my attempt to fix the problem by doing CAST(NEW.targetedfamily AS BOOLEAN) does NOT seem to work)

CREATE OR REPLACE FUNCTION fn_family_audit() RETURNS TRIGGER AS $tr_family_audit$
    BEGIN
        --
        -- Create a row in family_audit to reflect the operation performed on family,
        -- make use of the special variable TG_OP to work out the operation.
        --
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO public.family_audit values (
         DEFAULT, 'D', OLD.family_id, OLD.familyserialno, OLD.node_id, OLD.sourcetype, OLD.familyname,  
        OLD.familynamelocallang, OLD.hofname, OLD.hofnamelocallang, OLD.targetedfamily, OLD.homeless,
        OLD.landless, OLD.dependentonlabour, OLD.womenprimaryearner, OLD.landlinenumber, OLD.username , now());
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO public.family_audit values(
         DEFAULT, 'U',NEW.family_id, NEW.familyserialno, NEW.node_id, NEW.sourcetype, NEW.familyname,   
        NEW.familynamelocallang, NEW.hofname, NEW.hofnamelocallang, NEW.targetedfamily, NEW.homeless,
        NEW.landless, NEW.dependentonlabour, NEW.womenprimaryearner, NEW.landlinenumber, NEW.username , now());
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO public.family_audit values(
         DEFAULT, 'I',NEW.family_id, NEW.familyserialno, NEW.node_id, NEW.sourcetype, NEW.familyname,   
        NEW.familynamelocallang, NEW.hofname, NEW.hofnamelocallang, CAST(NEW.targetedfamily AS BOOLEAN), NEW.homeless,
        NEW.landless, NEW.dependentonlabour, NEW.womenprimaryearner, NEW.landlinenumber, NEW.username , now());
            RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$tr_family_audit$ LANGUAGE plpgsql;

Here's the table definition

nucleus4=# \d family;
                                             Table "public.family"
       Column        |            Type             |                         Modifiers
---------------------+-----------------------------+------------------------------------------------------------
 family_id           | integer                     | not null default nextval('family_family_id_seq'::regclass)
 familyserialno      | integer                     | not null
 sourcetype          | character varying(20)       | not null
 familyname          | character varying(100)      |
 familynamelocallang | character varying(255)      |
 hofname             | character varying(100)      | not null
 hofnamelocallang    | character varying(255)      | not null
 targetedfamily      | boolean                     |
 homeless            | boolean                     |
 landless            | boolean                     |
 dependentonlabour   | boolean                     |
 womenprimaryearner  | boolean                     |
 landlinenumber      | character varying(20)       |
 username            | character varying(20)       | not null
 adddate             | timestamp without time zone | not null default now()
 updatedate          | timestamp without time zone | not null default now()
 node_id             | integer                     | not null
Indexes:
    "PK_family" PRIMARY KEY, btree (family_id)
    "family_idx" UNIQUE, btree (familyserialno, node_id)
Foreign-key constraints:
    "family_fk" FOREIGN KEY (node_id) REFERENCES hierarchynode_master(node_id)
Referenced by:
    TABLE "agriland" CONSTRAINT "FK_agriland_family" FOREIGN KEY (family_id) REFERENCES family(family_id) ON UPDATE RESTRICT ON DELETE RESTRICT
    TABLE "currentloans" CONSTRAINT "FK_currentloans_family" FOREIGN KEY (family_id) REFERENCES family(family_id) ON UPDATE RESTRICT ON DELETE RESTRICT
    TABLE "family_address" CONSTRAINT "FK_family_address_family" FOREIGN KEY (family_id) REFERENCES family(family_id) ON UPDATE RESTRICT ON DELETE RESTRICT
    TABLE "family_basic_info" CONSTRAINT "FK_family_basic_info_family" FOREIGN KEY (family_id) REFERENCES family(family_id) ON UPDATE RESTRICT ON DELETE RESTRICT
    TABLE "family_entitlement" CONSTRAINT "FK_family_entitlement_family" FOREIGN KEY (family_id) REFERENCES family(family_id) ON UPDATE RESTRICT ON DELETE RESTRICT
    TABLE "livestock" CONSTRAINT "FK_livestock_family" FOREIGN KEY (family_id) REFERENCES family(family_id) ON UPDATE RESTRICT ON DELETE RESTRICT
    TABLE "member" CONSTRAINT "FK_member_family" FOREIGN KEY (family_id) REFERENCES family(family_id) ON UPDATE RESTRICT ON DELETE RESTRICT
    TABLE "otherassets" CONSTRAINT "FK_otherassets_family" FOREIGN KEY (family_id) REFERENCES family(family_id) ON UPDATE RESTRICT ON DELETE RESTRICT
Triggers:
    tr_family_audit AFTER INSERT OR DELETE OR UPDATE ON family FOR EACH ROW EXECUTE PROCEDURE fn_family_audit()
    tr_family_updatedate BEFORE UPDATE ON family FOR EACH ROW EXECUTE PROCEDURE fn_modify_updatedate_column()


nucleus4=#

Here's family_audit

nucleus4=# \d family_audit;
                                                Table "public.family_audit"
       Column        |            Type             |                               Mod
---------------------+-----------------------------+----------------------------------
 familyaudit_id      | integer                     | not null default nextval('family_
 operation           | character(1)                | not null
 family_id           | integer                     | not null
 familyserialno      | integer                     | not null
 sourcetype          | character varying(20)       | not null
 familyname          | character varying(100)      |
 familynamelocallang | character varying(255)      |
 hofname             | character varying(100)      | not null
 hofnamelocallang    | character varying(255)      | not null
 targetedfamily      | boolean                     |
 homeless            | boolean                     |
 landless            | boolean                     |
 dependentonlabour   | boolean                     |
 womenprimaryearner  | boolean                     |
 landlinenumber      | character varying(20)       |
 username            | character varying(20)       | not null
 adddate             | timestamp without time zone | not null default now()
 node_id             | integer                     | not null
Indexes:
    "PK_family_audit" PRIMARY KEY, btree (familyaudit_id)


nucleus4=#

Here's the trigger

CREATE TRIGGER tr_family_audit
AFTER INSERT OR UPDATE OR DELETE ON public.family
    FOR EACH ROW EXECUTE PROCEDURE fn_family_audit();

I would appreciate any hints.

Thank you,

BR,
~A

Answer 1

Your problem is here:

NEW.hofnamelocallang

Your insert has one extra column (apparently NEW.node_id). Try changing your insert to:

INSERT INTO public.family_audit values(
         DEFAULT, 'I',NEW.family_id, NEW.familyserialno, 
         NEW.sourcetype, NEW.familyname,   
         NEW.familynamelocallang, NEW.hofname, NEW.hofnamelocallang,
         NEW.targetedfamily, NEW.homeless,
         NEW.landless, NEW.dependentonlabour, NEW.womenprimaryearner, 
         NEW.landlinenumber, NEW.username , now()
);

The error you are getting is basically saying that you were trying to insert NEW.hofnamelocallang into targetedfamily column (which is boolean, not varchar) because of the extra column you were putting in the insert sentence.

I would advice that, when you are performing an insert, for sanity reasons, always enumerate the columns you are putting values into. Something like this:

insert into table foo
(col1, col2, col3) -- column enumeration here
values
(1, 2, 3);