Re: translating this SQL query from a different dialect - Mailing list pgsql-novice

On 1/10/06, Vanja <milosevski@gmail.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 ;)