Обсуждение: Help creating a function

Hi


I'm migrating an apllicattion taht use MS-Access to postgreSQL, many of the
SQL statements contains simple functions
like month(), date(), year(),  and so on , I don't want   to modify all the
statements (are about 200), so i'm creating functions in my postgres db with
this names and functionality, but i get errors.

For example, the function month

CREATE FUNCTION month(timestamp) RETURNS date AS '

  DECLARE
    fecha ALIAS FOR $1 ;
    mes int4;

  BEGIN
    SELECT INTO mes EXTRACT (MONTH FROM TIMESTAMP (fecha) );
    RETURN mes;

  END

' LANGUAGE PLPGSQL;

When executed results in :

SCE=# SELECT  month('2002-2-3');
NOTICE:  Error occurred while executing PL/pgSQL function month
NOTICE:  line 7 at select into variables
ERROR:  parser: parse error at or near "$1"

I don't known what i'm doing wrong so i need your help.


Thanks

Eduardo Cadena



_________________________________________________________________
Únase al mayor servicio mundial de correo electrónico:
http://www.hotmail.com/es

"Eduardo Cadena" <ecadenag@hotmail.com> writes:
> CREATE FUNCTION month(timestamp) RETURNS date AS '
>   DECLARE
>     fecha ALIAS FOR $1 ;
>     mes int4;
>   BEGIN
>     SELECT INTO mes EXTRACT (MONTH FROM TIMESTAMP (fecha) );
>     RETURN mes;
>   END
> ' LANGUAGE PLPGSQL;

Try just
     SELECT INTO mes EXTRACT (MONTH FROM fecha);

$1 already is a timestamp, and your attempt to do an unnecessary
coercion via function syntax is running into the problem that
TIMESTAMP(...) is a reserved syntax per SQL.

            regards, tom lane