Postgresql Using max function in trigger function

Question

--Problem is: i have more companies and i want to make id_shutter "autoincrement" from 1 for each company (id_company and id_shutter are composite PK)

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

Ondrej Ignác · Accepted Answer · 2016-02-07 13:02:10Z

2

sorry i found out, i cant use: new.id_shutter:=id_shutter+1; i made SELECT MAX(id_shutter)+1 INTO new.id_shutter FROM shutter where id_company=new.id_company;

answered Feb 7, 2016 at 13:02

Ondrej Ignác

312 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2016-02-07 12:56:37Z

0

Occasionally this is necessary. But really, it is much easier to add a serial column to the table (let's call it ShutterId) and then do the calculation when you retrieve the data:

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

Also, calling something an id that will take on duplicated values is misleading.

answered Feb 7, 2016 at 12:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Postgresql Using max function in trigger function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related