Postgresql在触发器函数中使用max函数

Question

--问题是:我有更多的公司，我想让每个公司的id_shutter从1开始自动递增(id_company和id_shutter是复合主键)

 CREATE FUNCTION insert_shutter() RETURNS TRIGGER AS $insert_shutter$
BEGIN
    IF exists(select 1 from shutter where id_company=new.id_company) then
    SELECT MAX(id_shutter) INTO new.id_shutter FROM shutter where id_company=new.id_company;
    new.id_shutter:=id_shutter+1;
    ELSE
    new.id_shutter=1;
    end if;
        RETURN NEW;

END;
$insert_shutter$ LANGUAGE plpgsql;

CREATE TRIGGER insert_shutter 
    BEFORE INSERT ON shutter
    FOR EACH ROW
    EXECUTE PROCEDURE insert_shutter();

Answer 1

很抱歉我发现了，我不能使用: new.id_shutter:=id_shutter+1；我让SELECT MAX(id_shutter)+1到new.id_shutter从快门的地方id_company=new.id_company；

Answer 2

有时这是必要的。但实际上，在表中添加一个serial列(让我们称之为ShutterId)，然后在检索数据时进行计算要容易得多：

select s.*, row_number() over (partition by company order by id) as CompanyShutterSequence
from shutter s;

此外，将具有重复值的对象称为id也是一种误导。