4

I want to write a Postgres SQL trigger that will basically find if a number appears in a column 5 or more times. If it appears a 5th time, I want to throw an exception. Here is how the table looks:

create table tab(
first integer not null constraint pk_part_id primary key,
second integer constraint fk_super_part_id references bom,
price integer);

insert into tab values(1,NULL,100), (2,1,50), (3,1,30), (4,2,20), (5,2,10), (6,3,20);

Above are the original inserts into the table. My trigger will occur upon inserting more values into the table.

Basically if a number appears in the 'second' column more than 4 times after inserting into the table, I want to raise an exception. Here is my attempt at writing the trigger:

create function check() return trigger as '
begin
    if(select first, second, price
          from tab
          where second in (
            select second from tab
            group by second
            having count(second) > 4)
) then
raise exception ''Error, there are more than 5 parts.'';
end if;
return null;
end 
'language plpgsql;

create trigger check
after insert or update on tab
for each row execute procedure check();

Could anyone help me out? If so that would be great! Thanks!

user1871869
  • 3,317
  • 13
  • 56
  • 106

1 Answers1

1
CREATE FUNCTION trg_upbef()
  RETURN trigger as
$func$
BEGIN
IF (SELECT count(*) 
    FROM   tab
    WHERE  second = NEW.second ) > 3 THEN

   RAISE EXCEPTION 'Error: there are more than 5 parts.';
END IF;

RETURN NEW;  -- must be NEW for BEFORE trigger

END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER upbef
BEFORE INSERT OR UPDATE ON tab
FOR EACH ROW EXECUTE procedure trg_upbef();

Major points

  • Keyword is RETURNS, not RETURN.

  • Use the special variable NEW to refer to the newly inserted / updated row.

  • Use a BEFORE trigger. Better skip early in case of an exception.

  • Don't count everything for your test, just what you need. Much faster.

  • Use dollar-quoting. Makes your live easier.

  • Concurrency:
    If you want to be absolutely sure, you'll have to take an exclusive lock on the table before counting. Else, concurrent inserts / updates might outfox each other under heavy concurrent load. While this is rather unlikely, it's possible.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Is there a reason why you put the > 3 after the if statement? – user1871869 Nov 18 '13 at 20:29
  • @user1871869: It isn't *after* the `IF` statement, really. Just outside the `SELECT` statement. The comparison with `count(*)` could go inside as well in this case. Would even be a very tiny bit faster. I did not enclose the whole `IF` expression in parentheses like you did. That's just noise. – Erwin Brandstetter Nov 18 '13 at 20:39
  • Makes a lot of sense. Thank you! I was having issues with the RETURN statement... I put return as opposed to returns.. – user1871869 Nov 18 '13 at 21:21