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
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 ===

In response to

Responses

Browse pgsql-novice by date

  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