Re: translating this SQL query from a different dialect

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