Thread: translating this SQL query from a different dialect
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.
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 ;)
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 ===
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
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 ===
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 ===
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
On 1/10/06, A. Kretschmer <andreas.kretschmer@schollglas.com> 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 ;)
am 10.01.2006, um 15:04:00 -0500 mailte Jaime Casanova folgendes: > On 1/10/06, A. Kretschmer <andreas.kretschmer@schollglas.com> 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 ===
thank you everyone for your help! works fine for what i need. On 1/10/06, A. Kretschmer <andreas.kretschmer@schollglas.com> 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 >