postgresql - 错误 : column "semester_id" is of type integer but expression is of type boolean

Question

我在 PostgreSQL 中创建了这个 PL/pgSQL 触发器函数:

CREATE or replace FUNCTION public.trigger31()
        RETURNS trigger
        LANGUAGE 'plpgsql'
        COST 100
        VOLATILE NOT LEAKPROOF 
    AS $BODY$
    begin
        if(TG_OP='INSERT') then
            if(New.start_date>(select max(end_date) from "Semester") and New.end_date>New.start_date) then

                Insert Into "Semester" (semester_id,academic_year,academic_season,start_date,end_date,semester_status)
                values (New.semester_id=(select max(s.semester_id)+1 from "Semester" s):: integer,
                        new.academic_year= academic_year_trig(),
                        new.academic_season=academic_season_trig(),
                        new.start_date,new.end_date,
                        new.semester_status=semester_stat_trig()
                    );
                return NEW;
            else
                RAISE EXCEPTION 'Invalid start_date or end_date';
            end if;
        end if;
    end;
$BODY$;

尝试执行它时，出现此错误:

ERROR:  column "semester_id" is of type integer but expression is of type boolean
LINE 2:    values (New.semester_id=(select max(s.semester_id)+1 from...

我不知道为什么。我 cast New.semester_id 的表达式并且我有同样的错误。也试过这个:

New.semester_id=(select max(s.semester_id):: integer +1 from "Semester" s)

我仍然有同样的错误。

Answer 1

好像你在混INSERT和 UPDATE语法错误。它可能像这样工作:

...
            INSERT INTO "Semester" (semester_id, academic_year, academic_season, start_date, end_date, semester_status)
            VALUES ((select max(s.semester_id) + 1 from "Semester" s),
                    academic_year_trig(),
                    academic_season_trig(),
                    new.start_date,
                    new.end_date,
                    semester_stat_trig()
                   );
...

但我怀疑您也误解了触发器的工作原理。如果该触发器函数用于表 “Semester” 上的触发器 ON INSERT，它会触发一个无限循环 - 除了通常是无意义的。细读关于triggers的说明书和 trigger functions .

除此之外，您似乎需要先阅读有关 serial 和 IDENTITY 列的信息:

• Auto increment table column