Re: Update columns in same table from update trigger? - Mailing list pgsql-general

From Alban Hertroys
Subject Re: Update columns in same table from update trigger?
Date
Msg-id 39387FA6-D473-4575-BC81-F9A4A1FEBE07@solfertje.student.utwente.nl
Whole thread Raw
In response to Update columns in same table from update trigger?  (Pablo Romero Abiti <pabiti@yahoo.com>)
Responses Re : Update columns in same table from update trigger?  (Pablo Romero Abiti <pabiti@yahoo.com>)
List pgsql-general
On 22 Jul 2011, at 22:02, Pablo Romero Abiti wrote:

> The problem I'm facing is that the trigger before update won't execute if there isn't a row with idcol=3 in the table
warehouse.
>
> Here's my code:
>
> CREATE OR REPLACE FUNCTION update_warehouse() returns "trigger" AS '
>    declare idcolmaestro float:=0;
>    BEGIN
>       select into a idcolor1 from color_eq where idcolor2=old.idcolor;

You didn't declare 'a'. Perhaps you meant to use idcolmaestro?

>       if a is null then

Try 'if NOT FOUND' here instead.

>          a=old.idcolor;
>       end if;
>
>       new.idcolor=a;
>       return new;
>   END;
> ' LANGUAGE 'plpgsql' VOLATILE;

I'd probably change this code a bit so that the assignment to idcolor only takes place if a value with
idcolor2=old.idvaluewas found: 

    if FOUND then
        new.idcolor := a;
    endif

    return new;

That saves a few unnecessary CPU cycles.

Alban Hertroys

--
The scale of a problem often equals the size of an ego.



!DSPAM:737,4e2a8d6912091302916282!



pgsql-general by date:

Previous
From: Darren Duncan
Date:
Subject: Re: Implementing "thick"/"fat" databases
Next
From: Alban Hertroys
Date:
Subject: Re: Implementing "thick"/"fat" databases