Thread: example of Create Function

Hi all

Like I told before, I'm coming from Interdabe!!

so, could anyone send me some examples of CREATE FUNCTION? (using SQL
language)

This is an Interbase sample:

CREATE PROCEDURE MY_PROC( P_DATE_START TIMESTAMP )
RETURNS ( R_DATE_PAYMENT TIMESTAMP, R_DESCRIPTION VHARCHAR(50), R_VALUE
NUMERIC (12,2))
AS
BEGIN
   FOR SELECT DATE_PAYMENT, R_DESCRIPTION,VALUE  FROM PAYMENTS
           WHERE DATE_PAYMENT BETWEEN :P_DATE_STAR AND 'TODAY'
        INTO
         :R_DATE_PAYMENT ,
         :R_DESCRIPTION ,
         :R_VALUE
   DO
   BEGIN
      /*here I can do any check for each row of SELECT below!! */

      SUSPEND; /* Each SUSPEND command returns a row */
   END

END^

I can use this stored proc like this:
    SELECT * FROM MY_PROC( '01/01/2002' )

and it may return many rows ...


tia


Roberto de Amorim

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

>
> 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:

by the way, when does it will be done???


>
> 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!! */


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


>        RETURN NEXT rec; /* Each RETURN NEXT command returns a row */
>     END LOOP;
>     RETURN;
> END;
> ' LANGUAGE 'plpgsql';
>
> See:
>
http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.htm
l
> and
>   http://developer.postgresql.org/docs/postgres/xfunc-tablefunctions.html
>
> HTH,
>
> Joe
>
>

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