Thread: 'plpgsql' oddity

This is probably something I'm doing (or not doing) but I can't seem to
figure out what it is... I have been playing with this little function :

CREATE FUNCTION invoice_payment() RETURNS OPAQUE AS '
BEGIN

   UPDATE invoice_master SET total = total - NEW.amount,updated = ''now''
WHERE invoice_id = NEW.invoice_id;

   RETURN NEW;

END;
' LANGUAGE 'plpgsql';


.. and this trigger :

CREATE TRIGGER invoice_trigger AFTER INSERT ON invoice_payments
FOR EACH ROW EXECUTE PROCEDURE invoice_payment();


The first field to be updated (total to total - amount) works just fine but
anything I put after that simply doesn't happen, it's very strange.. In the
above example, total gets updated but the field 'updated' does not, yet the
query does run (and correctly as far as the total field goes) so I'm
assuming there isn't a parse error or anything..

Can someone smack me with a clue stick?

Thanks!

-Mitch

"Mitch Vincent" <mitch@venux.net> writes:
> CREATE FUNCTION invoice_payment() RETURNS OPAQUE AS '
> BEGIN

>    UPDATE invoice_master SET total = total - NEW.amount,updated = ''now''
> WHERE invoice_id = NEW.invoice_id;

>    RETURN NEW;

> END;
> ' LANGUAGE 'plpgsql';

> total gets updated but the field 'updated' does not

Try updated = now().  The above is probably reducing 'now' to a
timestamp constant when the function is compiled ...

            regards, tom lane

I think you're running into constant evaluation issues.
The query plan's going to be generated when it first runs successfully
which means the constant 'now' is probably going to be evaluated at
that time.  Try using now() instead.

On Mon, 22 Jan 2001, Mitch Vincent wrote:

> This is probably something I'm doing (or not doing) but I can't seem to
> figure out what it is... I have been playing with this little function :
>
> CREATE FUNCTION invoice_payment() RETURNS OPAQUE AS '
> BEGIN
>
>    UPDATE invoice_master SET total = total - NEW.amount,updated = ''now''
> WHERE invoice_id = NEW.invoice_id;
>
>    RETURN NEW;
>
> END;
> ' LANGUAGE 'plpgsql';
>
>
> .. and this trigger :
>
> CREATE TRIGGER invoice_trigger AFTER INSERT ON invoice_payments
> FOR EACH ROW EXECUTE PROCEDURE invoice_payment();
>
>
> The first field to be updated (total to total - amount) works just fine but
> anything I put after that simply doesn't happen, it's very strange.. In the
> above example, total gets updated but the field 'updated' does not, yet the
> query does run (and correctly as far as the total field goes) so I'm
> assuming there isn't a parse error or anything..
>
> Can someone smack me with a clue stick?
>
> Thanks!
>
> -Mitch
>