From: | Jaime Casanova <systemguards(at)gmail(dot)com> |
---|---|
To: | Vanja <milosevski(at)gmail(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: translating this SQL query from a different dialect |
Date: | 2006-01-10 14:47:27 |
Message-ID: | c2d9e70e0601100647j7a6480c0g4ae6a52bf4af55b8@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On 1/10/06, Vanja <milosevski(at)gmail(dot)com> wrote:
> 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..
>
> Thank you.
>
CREATE FUNCTION eigth_records_limit() RETURNS TRIGGER AS $$
DECLARE
num_rows int4;
BEGIN
SELECT INTO num_rows COUNT(*) FROM table_name;
IF num_rows = 8 THEN
RETURN NULL;
END IF;
RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';
CREATE TRIIGER trg_eigth_records_limit BEFORE INSERT ON table_name
FOR EACH ROW EXECUTE PROCEDURE eigth_records_limit();
but i think a field to number the rows with not null, unique, check
constraints it's enough for doing the job
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
From | Date | Subject | |
---|---|---|---|
Next Message | A. Kretschmer | 2006-01-10 14:53:00 | Re: translating this SQL query from a different dialect |
Previous Message | Danjel Jungersen | 2006-01-10 14:02:05 | Re: Preventing access of user1 to user2's database |