Re: translating this SQL query from a different dialect - Mailing list pgsql-novice

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