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.

Search Discussions

  • Jaime Casanova at Jan 10, 2006 at 3:31 pm

    On 1/10/06, Vanja 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 ;)
  • Andreas Kretschmer at Jan 10, 2006 at 3:31 pm

    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 ===
  • Michael Fuhr at Jan 10, 2006 at 5:39 pm

    On Tue, Jan 10, 2006 at 03:53:00PM +0100, A. Kretschmer wrote:
    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();
    Beware that as written this doesn't handle concurrency. For example:

    Transaction A: BEGIN;
    Transaction B: BEGIN;
    Transaction A: INSERT INTO foo ... -- 7 times
    Transaction B: INSERT INTO foo ... -- 7 times
    Transaction A: COMMIT;
    Transaction B: COMMIT;

    The table now has 14 records. You'll need to add some extra locking
    for it to work in a concurrent environment.

    --
    Michael Fuhr
  • Andreas Kretschmer at Jan 10, 2006 at 6:28 pm

    am 10.01.2006, um 10:38:59 -0700 mailte Michael Fuhr folgendes:
    create trigger max8 before insert on foo for each row execute procedure max8();
    Beware that as written this doesn't handle concurrency. For example:

    The table now has 14 records. You'll need to add some extra locking
    for it to work in a concurrent environment.
    Oh, thanks very much for this hint.


    Andreas
    --
    Andreas Kretschmer (Kontakt: siehe Header)
    Heynitz: 035242/47212, D1: 0160/7141639
    GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
    === Schollglas Unternehmensgruppe ===
  • Vanja at Jan 10, 2006 at 9:04 pm
    thank you everyone for your help!

    works fine for what i need.

    On 1/10/06, A. Kretschmer wrote:
    am 10.01.2006, um 10:38:59 -0700 mailte Michael Fuhr folgendes:
    create trigger max8 before insert on foo for each row execute procedure max8();
    Beware that as written this doesn't handle concurrency. For example:

    The table now has 14 records. You'll need to add some extra locking
    for it to work in a concurrent environment.
    Oh, thanks very much for this hint.


    Andreas
    --
    Andreas Kretschmer (Kontakt: siehe Header)
    Heynitz: 035242/47212, D1: 0160/7141639
    GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
    === Schollglas Unternehmensgruppe ===

    ---------------------------(end of broadcast)---------------------------
    TIP 5: don't forget to increase your free space map settings
  • Andreas Kretschmer at Jan 10, 2006 at 7:06 pm

    am 10.01.2006, um 10:38:59 -0700 mailte Michael Fuhr folgendes:
    Beware that as written this doesn't handle concurrency. For example:
    Please, can you expand this example that it works with concurrency?

    This is a new field for me...

    Thank you, Andreas
    --
    Andreas Kretschmer (Kontakt: siehe Header)
    Heynitz: 035242/47212, D1: 0160/7141639
    GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
    === Schollglas Unternehmensgruppe ===
  • Andreas Kretschmer at Jan 10, 2006 at 8:01 pm

    am 10.01.2006, um 20:06:26 +0100 mailte A. Kretschmer folgendes:
    am 10.01.2006, um 10:38:59 -0700 mailte Michael Fuhr folgendes:
    Beware that as written this doesn't handle concurrency. For example:
    I'm not sure, if this enough to prevent problems:

    ...
    begin
    lock table foo in EXCLUSIVE mode ;
    select into c count(*) from foo;
    ...

    All other lines are unchanged.


    I think/hope, i can prevent all problems, but i'm not sure if there are
    problems with Deadlocks.


    thanks for the response, Andreas
    --
    Andreas Kretschmer
    Kontakt: Heynitz: 035242/47212, D1: 0160/7141639 (mehr: -> Header)
    GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
    eMail schreiben kann jeder -- lernen: http://webserv/email/email.html
  • Jaime Casanova at Jan 10, 2006 at 8:04 pm

    On 1/10/06, A. Kretschmer wrote:
    am 10.01.2006, um 10:38:59 -0700 mailte Michael Fuhr folgendes:
    Beware that as written this doesn't handle concurrency. For example:
    Please, can you expand this example that it works with concurrency?

    This is a new field for me...
    and what's the problem with something like

    create table foo (
    fld1 int4 not null unique
    constraint eight_rows_limit check (fld1 >= 0 and fld1 <= 8)
    );

    --
    regards,
    Jaime Casanova
    (DBA: DataBase Aniquilator ;)
  • Andreas Kretschmer at Jan 10, 2006 at 8:11 pm

    am 10.01.2006, um 15:04:00 -0500 mailte Jaime Casanova folgendes:
    On 1/10/06, A. Kretschmer wrote:
    am 10.01.2006, um 10:38:59 -0700 mailte Michael Fuhr folgendes:
    Beware that as written this doesn't handle concurrency. For example:
    Please, can you expand this example that it works with concurrency?

    This is a new field for me...
    and what's the problem with something like

    create table foo (
    fld1 int4 not null unique
    constraint eight_rows_limit check (fld1 >= 0 and fld1 <= 8)
    );
    nice ;-)


    Andreas
    --
    Andreas Kretschmer (Kontakt: siehe Header)
    Heynitz: 035242/47212, D1: 0160/7141639
    GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
    === Schollglas Unternehmensgruppe ===

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJan 10, '06 at 2:29p
activeJan 10, '06 at 9:04p
posts10
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase