代码之家 › 专栏 › 技术社区 › anjanb

postgresql 8.4触发器/存储过程错误:如何修复?

casting stored-procedures postgresql

anjanb · 技术社区 · 14 年前

表上的插入会触发存储过程,其中会发生以下错误。

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

下面是错误的存储过程(请注意,我试图通过执行CAST来修复问题)(新目标家庭作为布尔值)似乎不起作用)

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;

这是表格定义

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=#

这是家庭审计

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=#

这是扳机

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

如有任何提示,我将不胜感激。

比尔,

1 回复 | 直到 14 年前

Pablo Santa Cruz 14 年前

你的问题是:

NEW.hofnamelocallang

您的insert有一个额外的列(显然 NEW.node_id ). 尝试将插入内容更改为:

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()
);

您得到的错误基本上是说您试图插入 NEW.hofnamelocallang 进入之内 targetedfamily 列(这是布尔值,而不是varchar),因为您在insert语句中添加了额外的列。

我建议,在执行插入时,出于理智的考虑,请始终枚举要将值放入的列。像这样:

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