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

am  10.01.2006, um 14:04:56 +0100 mailte Vanja folgendes:
> I have the following query which I would need to be able to use in
> PostgreSQL. This basically limits the number of allowed rows in a
> table to 8.
>
>   CREATE TRIGGER <TRIGGERNAME> ON [<TABLENAME>]
>   FOR INSERT AS
>   BEGIN DECLARE @<VARIABLENAME1> INT
>   SELECT <VARIABLENAME1> = COUNT (*) FROM <TABLENAME>
>   IF ( @<VARIABLENAME1>) > 8
>   BEGIN RAISERROR ('<ERROR MESSAGE>', 16, 1)
>   ROLLBACK TRANSACTION
>   RETURN
>   END
>   END
>
> I've tried various combinations but none of them seem to work... any
> help to convert this to PostgreSQL would be highly appreciated..

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


tablename is 'foo', trigger-function is max8().


HTH, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47212,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===