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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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 ===
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-01-10 15:19:38 | Re: With auto vacuum, is analyze still necessary? |
Previous Message | Jaime Casanova | 2006-01-10 14:47:27 | Re: translating this SQL query from a different dialect |