Skip site navigation (1) Skip section navigation (2)

Re: translating this SQL query from a different dialect

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: translating this SQL query from a different dialect
Date: 2006-01-10 14:53:00
Message-ID: 20060110145300.GI22237@webserv.wug-glas.de (view raw or flat)
Thread:
Lists: 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    === 

In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2006-01-10 15:19:38
Subject: Re: With auto vacuum, is analyze still necessary?
Previous:From: Jaime CasanovaDate: 2006-01-10 14:47:27
Subject: Re: translating this SQL query from a different dialect

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group