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

Hi List,


   I was wondering if someone could help me out. I'm migrating a Oracle db to
PostgreSQL so I must rewrite the functions 'cuz the PL/pgSQL's sintax is
different. The problem is to sum dates .. as follows:

CREATE FUNCTION ft_vcto_dupl(NUMERIC,NUMERIC,TIMESTAMP,NUMERIC,TIMESTAMP)
RETURNS TIMESTAMP AS '
   DECLARE
   pEmp ALIAS FOR $1;
   pFil ALIAS FOR $2;
   pDataEmissao ALIAS FOR $3;
   pCodigoPrazoPgto ALIAS FOR $4;
   pPrazoPgtoFixo ALIAS FOR $5;
   mDataVctoDupl TIMESTAMP := pPrazoPgtoFixo;
   mPrazoPgto NUMERIC(3) := 0;
   BEGIN
     SELECT INTO mPrazoPgto prazo FROM ftpraz00 WHERE emp = pEmp AND fil = 0
AND codigo = pCodigoPrazoPgto;
     IF NVL(to_number(TO_CHAR(mDataVctoDupl,''MM'')),00) = 00 THEN
*****   mDataVctoDupl := pDataEmissao + mPrazoPgto;
     END IF;
     --
     RETURN (mDataVctoDupl);
   END;

' LANGUAGE 'plpgsql'

    In the line marked with  " ***** "  it should be something like :

     mDataVctoDupl := pDataEmissao + interval ''mPrazoPgto day'';

    BUT , this way the PostgreSQL treats the variable mPrazoPgto as a string
and it must treat it like a variable and use its value.

   How do I make it understand mPrazoPgto as variable ?

Atenciosamente,

Rhaoni Chiu Pereira
Sistêmica Computadores

Visite-nos na Web: http://sistemica.info
Fone/Fax : +55 51 3328 1122

On Tue, 19 Aug 2003, Rhaoni Chiu Pereira wrote:

> Hi List,
>
>
>    I was wondering if someone could help me out. I'm migrating a Oracle db to
> PostgreSQL so I must rewrite the functions 'cuz the PL/pgSQL's sintax is
> different. The problem is to sum dates .. as follows:
>
> CREATE FUNCTION ft_vcto_dupl(NUMERIC,NUMERIC,TIMESTAMP,NUMERIC,TIMESTAMP)
> RETURNS TIMESTAMP AS '
>    DECLARE
>    pEmp ALIAS FOR $1;
>    pFil ALIAS FOR $2;
>    pDataEmissao ALIAS FOR $3;
>    pCodigoPrazoPgto ALIAS FOR $4;
>    pPrazoPgtoFixo ALIAS FOR $5;
>    mDataVctoDupl TIMESTAMP := pPrazoPgtoFixo;
>    mPrazoPgto NUMERIC(3) := 0;
>    BEGIN
>      SELECT INTO mPrazoPgto prazo FROM ftpraz00 WHERE emp = pEmp AND fil = 0
> AND codigo = pCodigoPrazoPgto;
>      IF NVL(to_number(TO_CHAR(mDataVctoDupl,''MM'')),00) = 00 THEN
> *****   mDataVctoDupl := pDataEmissao + mPrazoPgto;
>      END IF;
>      --
>      RETURN (mDataVctoDupl);
>    END;
>
> ' LANGUAGE 'plpgsql'
>
>     In the line marked with  " ***** "  it should be something like :
>
>      mDataVctoDupl := pDataEmissao + interval ''mPrazoPgto day'';

Perhaps you want something like:
pDataEmissao + interval ''1 day'' * mPrazoPgto;