Re: Trigger functions - Mailing list pgsql-general

Brilliant, absolutelly brilliant!!!!!!!!

It works.  Your absolutely right.  The update part of the trigger function was in the form:

BEGIN
UPDATE PHOTO SET GEOMETRY = (function_polygon(new.A,new.B,new.C));
RETURN NEW;
END;

notice the absence of the where clause.

now the trigger function looks like this:

BEGIN
new.GEOMETRY = (function_polygon(new.A,new.B,new.C));
RETURN NEW;
END;

and of course the trigger itself has been converted to a before insert or update.

Thanks a lot,

Marc

PS:  i'm using plpgsql

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Jan Wieck
Sent: Wednesday, April 02, 2003 2:41 PM
To: marc.drouin@fondex.ca
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Trigger functions

> Marc Drouin wrote:
>
> Hi Jan,
>
> The trigger function updates the table.  At the request of Tom Lane I
> will post the actual table and trigger
> function for you guys to have a better idea.

Gives me a fair enough idea of what's going on. Your trigger will get
fired for every single row inserted and does (probably) an unconditional
recomputation of ALL derived values for the WHOLE table ... let me
guess, an UPDATE on that table without a WHERE clause runs approximately
2 seconds, right ;-)

Change the thing into a BEFORE INSERT trigger, don't use UPDATE, assign
the computed values to the NEW row (just do "NEW.column := <expr>") and
finally let the trigger RETURN NEW.

Jan

>
> Marc
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Jan Wieck
> Sent: Tuesday, April 01, 2003 10:44 PM
> To: Tom Lane
> Cc: marc.drouin@fondex.ca; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Trigger functions
>
> Tom Lane wrote:
> >
> > Marc Drouin <marc.drouin@fondex.ca> writes:
> > > The function is triggered on insert and is supposed to fill a
> field in the
> > > table based on three other fields of the same table (ex: field D =
> Field A X
> > > Field B X Field C).  The addition of the trigger to teh table has
> slowed
> > > down the insert procedd considerably.  It takes approximatly two
> second for
> > > a simple insert.
> >
> > That's pretty slow, all right, but if you're not going to show us
> the
> > details there's not much advice we can offer.
>
> Does the trigger update the table or the pseudo row NEW?
>
> Jan
>
> --
> #======================================================================#
>
> # It's easier to get forgiveness for being wrong than for being right.
> #
> # Let's break this rule - forgive me.
> #
> #================================================== JanWieck@Yahoo.com
> #
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)