Re: example of Create Function

Roberto (SmartBit) wrote:
(re: PostgreSQL 7.3 release)
> by the way, when does it will be done???
>

Can't say for sure, but I think beta testing is starting to wind down.

>
> would I be able to change the value of field? like:
> IF r_value < 0 THEN
> r_value = r_value*-1;
>

Sure:

CREATE TABLE payments (r_date_payment TIMESTAMP, r_description VARCHAR(50),
r_value numeric (12,2));

INSERT INTO payments VALUES(CURRENT_TIMESTAMP, 'a', '12.50');
INSERT INTO payments VALUES(CURRENT_TIMESTAMP, 'b', '11.75');
INSERT INTO payments VALUES(CURRENT_TIMESTAMP, 'c', '-99.99');

CREATE OR REPLACE FUNCTION my_proc(TIMESTAMP)
RETURNS SETOF payments
AS '
DECLARE
rec RECORD;
BEGIN
FOR rec IN SELECT * FROM payments
WHERE r_date_payment BETWEEN $1 AND CURRENT_TIMESTAMP LOOP
IF rec.r_value < 0 THEN
rec.r_value = rec.r_value*-1;
END IF;
RETURN NEXT rec; /* Each RETURN NEXT command returns a row */
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';

test=# select * from payments;
r_date_payment | r_description | r_value
----------------------------+---------------+---------
2002-10-22 10:27:38.086554 | a | 12.50
2002-10-22 10:27:38.172964 | b | 11.75
2002-10-22 10:27:38.177543 | c | -99.99
(3 rows)

test=# SELECT * FROM my_proc('01/01/2002');
r_date_payment | r_description | r_value
----------------------------+---------------+---------
2002-10-22 10:27:38.086554 | a | 12.50
2002-10-22 10:27:38.172964 | b | 11.75
2002-10-22 10:27:38.177543 | c | 99.99
(3 rows)

Joe