Re: example of Create Function

Roberto (SmartBit) wrote:
> so, could anyone send me some examples of CREATE FUNCTION? (using SQL
> language)
>
> This is an Interbase sample:
[... Interbase example ...]

Your example is not possible with any PL in PostgreSQL versions prior to 7.3
(currently in beta testing). However, in 7.3, using PL/pgSQL it looks like this:

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');

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
/*here I can do any check for each row of SELECT above!! */
RETURN NEXT rec; /* Each RETURN NEXT command returns a row */
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';

test=# SELECT * FROM my_proc('01/01/2002');
r_date_payment | r_description | r_value
----------------------------+---------------+---------
2002-10-21 21:17:57.518038 | a | 12.50
2002-10-21 21:18:05.042573 | b | 11.75
(2 rows)

See:
http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html
and
http://developer.postgresql.org/docs/postgres/xfunc-tablefunctions.html

HTH,

Joe