pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Antoine Reid <antoiner@hansonpublications.com>
To: Madel, Kurt <KMadel@USInspect.com>
Cc: 'Antoine Reid' <antoiner@hansonpublications.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Use a rule or a transaction
Date: Tue, 15 Aug 2000 15:49:13 -0400
Message-ID: <20000815154913.A22168@wumpus.lan.edmarketing.com> (raw)
In-Reply-To: <C6F9B91B745CD4119F1500A0C9DD60C02E3490@exchhq01.usinspect.com>
References: <C6F9B91B745CD4119F1500A0C9DD60C02E3490@exchhq01.usinspect.com>

On Tue, Aug 15, 2000 at 03:38:09PM -0400, Madel, Kurt wrote:
> Hey Antoine,
> 
> I am using 7.0.2, and I would be in your debt if you created a working
> example.  Basically, the structure of the query I would like to do is as
> such (there are six modules in the module table for each quarter:
> a_q1,b_q1,c_q1,d_q1,e_q1,morn_q1,a_q2.....).  A student comes to register
> and selects all of the classes that are available in a given module, and
> then hits submit.  Information will be inserted into the modules table as
> follows (in PHP):

[snip]
I am not really familiar with PHP (installing/admining, but not programming 
with it...)..  At any rate, you will want to check the success/failure of 
every insert/update/delete...

Here is a working example of such tables, including the reference count AND
limit check.  This was tested with 7.0.2 on linux, i386.  Make sure you have
plpgsql installed in that database....

> If you could give me a working example of a trigger/function that would
> simplify this, that would be fantastic.

here we go, broken in sections:

# Here, the tables:
# I made those simple for demonstration purposes.

CREATE TABLE "modules" (
        "name" character varying(32) NOT NULL,
        "class_id" int4 NOT NULL,
        PRIMARY KEY ("name")
);

CREATE TABLE "classes" (
        "class_id" int4 NOT NULL,
        "usage" int4 NOT NULL,
        "max" int4 NOT NULL,
        PRIMARY KEY ("class_id")
);

# Now, the different plpgsql functions:

# this one will increment usage in a class.
CREATE FUNCTION "increment_classes" ( ) RETURNS opaque AS '
begin
update classes set usage = usage + 1 where classes.class_id = NEW.class_id;
return NEW;
end;
' LANGUAGE 'plpgsql';

# this one will decrement usage in a class.
CREATE FUNCTION "decrement_classes" ( ) RETURNS opaque AS '
begin
update classes set usage = usage - 1 where classes.class_id = OLD.class_id;
return OLD;
end;
' LANGUAGE 'plpgsql';

# this one will check if usage is under 0, or over the maximum.
CREATE FUNCTION "check_limit" ( ) RETURNS opaque AS '
begin
IF NEW.usage > NEW.max
then raise exception ''That class is full, sorry.'';
end if;
if NEW.usage < ''0''
then raise exception ''Class cannot have a negative value! Report to the DBA now!!'';
end if;
return new;
end;
' LANGUAGE 'plpgsql';


# now, let's create triggers to call those functions on some events:
CREATE TRIGGER "insert_modules" AFTER INSERT ON "modules"  FOR EACH ROW EXECUTE PROCEDURE "increment_classes" ();

CREATE TRIGGER "delete_modules" BEFORE DELETE ON "modules"  FOR EACH ROW EXECUTE PROCEDURE "decrement_classes" ();

CREATE TRIGGER "update_classes" AFTER UPDATE ON "classes"  FOR EACH ROW EXECUTE PROCEDURE "check_limit" ();



So there you go.. please note the following:

- The classes table should have a trigger to make sure you don't insert a row
with a usage != 0.

- There should be a trigger on UPDATE on modules that would decrement the old
class, and increment the new one.  The triggers on classes will already check
that the new one is not already full..

- The trigger on INSERT on modules should return an error if the class_id
does not exist..


These are left as an exercise to the reader... (especially because i'm not sure how to do that 
last one... :-> )

> Thanks,
> Kurt

hope this helps
antoine

-- 
 o          Antoine Reid             o>    Alcohol and calculus   <o>
<|> antoiner@hansonpublications.com <|    don't mix. Never drink   |
 >\    antoiner@edmarketing.com      >\         and derive.       /<



view thread (8+ messages)  latest in thread

Message-ID: <20000815154913.A22168@wumpus.lan.edmarketing.com>
Permalink:  ../20000815154913.A22168@wumpus.lan.edmarketing.com/
Also on:    postgresql.org/message-id/20000815154913.A22168@wumpus.lan.edmarketing.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: antoiner@hansonpublications.com, KMadel@USInspect.com
  Subject: Re: Use a rule or a transaction
  In-Reply-To: <20000815154913.A22168@wumpus.lan.edmarketing.com>
  References: <C6F9B91B745CD4119F1500A0C9DD60C02E3490@exchhq01.usinspect.com> <20000815154913.A22168@wumpus.lan.edmarketing.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox