Skip site navigation (1) Skip section navigation (2)

Re: translating this SQL query from a different dialect

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 (view raw or flat)
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 ;)

In response to

pgsql-novice by date

Next:From: A. KretschmerDate: 2006-01-10 14:53:00
Subject: Re: translating this SQL query from a different dialect
Previous:From: Danjel JungersenDate: 2006-01-10 14:02:05
Subject: Re: Preventing access of user1 to user2's database

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group