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)
