Postgres Pro
Downloads
Home   >   mailing lists

Thread: Another plpgsql question..

Another plpgsql question..

From
"Mitch Vincent"
Date:
CREATE FUNCTION invoice_payment() RETURNS OPAQUE AS '
BEGIN

   UPDATE invoice_master SET total = total - NEW.amount,updated = now(),
is_paid=(CASE WHEN (total - NEW.amount) = 0.00 THEN ''t'' ELSE ''f'' END)
WHERE invoice_id = NEW.invoice_id;

   RETURN NEW;

END;
' LANGUAGE 'plpgsql';

Ok, thanks for the heads up on now().. That works but I add the last part of
the query in there and everything but the last part works.. Can I not do
CASE WHEN statements in functions like that? That query works when I run it
straight from psql but so did the other, so I'm wondering if the same kind
of problem is happening here too..

Thanks!

-Mitch

Re: Another plpgsql question..

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

>    UPDATE invoice_master SET total = total - NEW.amount,updated = now(),
> is_paid=(CASE WHEN (total - NEW.amount) = 0.00 THEN ''t'' ELSE ''f'' END)
> WHERE invoice_id = NEW.invoice_id;

>    RETURN NEW;

> END;
> ' LANGUAGE 'plpgsql';

> Ok, thanks for the heads up on now().. That works but I add the last part of
> the query in there and everything but the last part works.. Can I not do
> CASE WHEN statements in functions like that? That query works when I run it
> straight from psql but so did the other, so I'm wondering if the same kind
> of problem is happening here too..

It looks fine to me.  Please define "everything but the last part works"
more precisely.

            regards, tom lane

Re: Another plpgsql question..

From
"Mitch Vincent"
Date:
is_paid is never updated...

-Mitch


----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Mitch Vincent" <mitch@venux.net>
Cc: <pgsql-general@postgresql.org>
Sent: Tuesday, January 23, 2001 10:56 AM
Subject: Re: [GENERAL] Another plpgsql question..


> "Mitch Vincent" <mitch@venux.net> writes:
> > CREATE FUNCTION invoice_payment() RETURNS OPAQUE AS '
> > BEGIN
>
> >    UPDATE invoice_master SET total = total - NEW.amount,updated = now(),
> > is_paid=(CASE WHEN (total - NEW.amount) = 0.00 THEN ''t'' ELSE ''f''
END)
> > WHERE invoice_id = NEW.invoice_id;
>
> >    RETURN NEW;
>
> > END;
> > ' LANGUAGE 'plpgsql';
>
> > Ok, thanks for the heads up on now().. That works but I add the last
part of
> > the query in there and everything but the last part works.. Can I not do
> > CASE WHEN statements in functions like that? That query works when I run
it
> > straight from psql but so did the other, so I'm wondering if the same
kind
> > of problem is happening here too..
>
> It looks fine to me.  Please define "everything but the last part works"
> more precisely.
>
> regards, tom lane
>

Re: Another plpgsql question..

From
Tom Lane
Date:
"Mitch Vincent" <mitch@venux.net> writes:
> is_paid is never updated...

It's not possible that is_paid is never updated; that command *will*
replace the total, updated, and is_paid columns with *something*.
It may be that in the cases you've checked, it gets updated to the
same value it had before.  That's why I want to see the test cases.

            regards, tom lane

Re: Another plpgsql question..

From
"Mitch Vincent"
Date:
Ok, it appears now I have an error.. Unless I'm going crazy, this started
after I had to do a restore because of one of those cache lookup errors.. I
changed nothing, still, this is what I get..


CREATE FUNCTION invoice_payment() RETURNS OPAQUE AS '
BEGIN

   UPDATE invoice_master SET total = total - NEW.amount,updated = now(),
is_paid=(CASE WHEN (total - NEW.amount) = 0.00 THEN ''t'' ELSE ''f'' END)
WHERE invoice_id = NEW.invoice_id;

   RETURN NEW;

END;
' LANGUAGE 'plpgsql';


Now I get

brw=# INSERT into invoice_payments VALUES
(1,1000,'now',100,'now',100,1,1,150.00);
ERROR:  Unable to identify an operator '=' for types 'numeric' and 'float8'
        You will have to retype this query using an explicit cast
DEBUG:  Last error occured while executing PL/pgSQL function invoice_payment
DEBUG:  line 2 at SQL statement
ERROR:  Unable to identify an operator '=' for types 'numeric' and 'float8'
        You will have to retype this query using an explicit cast
brw=#

--- amount and total are both numeric(9,2), I've tried casting everything
(total,amount and 0.00) to float and everything to numeric with the same
error popping up.. What needs casting here?

I can determine if an invoice has been paid or not a number of ways, really
what I should do there is NEW.amount >= total -- I tried and got the above
error again..

Thanks!

-Mitch

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Mitch Vincent" <mitch@venux.net>
Cc: <pgsql-general@postgresql.org>
Sent: Tuesday, January 23, 2001 11:05 AM
Subject: Re: [GENERAL] Another plpgsql question..


> "Mitch Vincent" <mitch@venux.net> writes:
> > is_paid is never updated...
>
> It's not possible that is_paid is never updated; that command *will*
> replace the total, updated, and is_paid columns with *something*.
> It may be that in the cases you've checked, it gets updated to the
> same value it had before.  That's why I want to see the test cases.
>
> regards, tom lane
>

Re: Another plpgsql question..

From
Tom Lane
Date:
"Mitch Vincent" <mitch@venux.net> writes:
> ERROR:  Unable to identify an operator '=' for types 'numeric' and 'float8'
>         You will have to retype this query using an explicit cast

> --- amount and total are both numeric(9,2),

Hm, then "(total - NEW.amount) = 0.00::numeric" should work, although
I don't see why you don't just write it as "total = NEW.amount".

            regards, tom lane