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