Re: translating this SQL query from a different dialect

On Tue, Jan 10, 2006 at 03:53:00PM +0100, A. Kretschmer wrote:
> create function max8() returns trigger as $$
> declare
> c int;
> begin
> select into c count(*) from foo;
> -- the name of the table is foo
>
> if (c = 8) then
> -- 8 is the maximum
> raise exception 'max count reached';
> end if;
> return NEW;
> end;
> $$ language plpgsql;
>
> create trigger max8 before insert on foo for each row execute procedure max8();

Beware that as written this doesn't handle concurrency. For example:

Transaction A: BEGIN;
Transaction B: BEGIN;
Transaction A: INSERT INTO foo ... -- 7 times
Transaction B: INSERT INTO foo ... -- 7 times
Transaction A: COMMIT;
Transaction B: COMMIT;

The table now has 14 records. You'll need to add some extra locking
for it to work in a concurrent environment.

--
Michael Fuhr