Forum OpenACS Development: Global temporary table, trigger procedure

1: Global temporary table, trigger procedure

Posted by Gilbert Wong on 07/06/01 08:00 PM

I have two questions:

1. How do I port "create global temporary table"? When I try creating a table using global temporary table, PostgreSQL complains and says that it's not supported yet.

2. Here is a trigger in Oracle which uses the "OF" keyword. I looked through the PostgreSQL docs and I believe the "OF" keyword is not supported. Should I remove "OF amount_used, amount_reinstated" or is there another way I should handle it?

create or replace trigger ec_gift_cert_amount_remains_tr
  before update OF amount_used, amount_reinstated on 
ec_gift_certificate_usage FOR each row
...

2: Response to Global temporary table, trigger procedure (response to 1)

Posted by Don Baccus on 07/06/01 08:21 PM

You can't specify a trigger on a subset of the columns in PG, so you need to define the trigger on the entire table instead.

If most updates to the table don't alter amount_used and amount_reinstated, it would be more efficient to do something like this:

if new.amount_used = old.amount_used and new.amount_reinstated = old.amount_reinstated then return new;

i.e. only do the update if the columns have actually changed.

As far as the create global temporary table that's done to avoid an Oracle restriction and isn't necessary in PG. The easiest way to port this chunk is probably to go to the OpenACS 3.2.5 sources and grab the previously ported code.