Thread: PGSQL function question

I have created a pgpsql function but it does not return the expected
value. It's a simple sql query, and if I do the same query by hand I get
the expected result so I can't see what is wrong.

Can someone offer some advice? Thanks :)

My function:

create or replace function member_points_to_be_refunded(integer) returns
integer as '
   declare points_used integer := 0;
   begin
     SELECT into points_used sum(points_used)
     FROM invoices
     WHERE
            member_id=$1
       AND payment_rcvd
       AND cancelled;
     IF points_used IS NULL THEN
       RETURN 0;
     END IF;
     RETURN points_used;
   end;
' language 'plpgsql' with (iscachable);

My tests:

TAL=# select member_points_to_be_refunded(9);
  member_points_to_be_refunded
------------------------------
                             0
(1 row)

TAL=# select sum(points_used) from invoices where member_id=9 and
payment_rcvd and cancelled;
  sum
-----
  100
(1 row)

TAL=# select points_used, cancelled from invoices where id=52;
  points_used | cancelled
-------------+-----------
          100 | t
(1 row)

Jean-Christian Imbeault wrote:
> I have created a pgpsql function but it does not return the expected
> value. It's a simple sql query, and if I do the same query by hand I get
> the expected result so I can't see what is wrong.

> create or replace function member_points_to_be_refunded(integer) returns
> integer as '
>    declare points_used integer := 0;
>    begin
>      SELECT into points_used sum(points_used)

You are using the same name for the local variable inside the function
as the column name, therefore you are actually summing the local variable.
Use a different name, or qualify the column name i.e.
sum(invoices.points_used)
--
Peter Gibbs
EmKel Systems

Peter Gibbs wrote:
>
> You are using the same name for the local variable inside the function
> as the column name

Doh!

Thanks :)

Jc