Обсуждение: Error in Trigger function. How to correct?

Hi,

I have a trigger that runs in my Development machine but not in my Production machine. the code is the following:

[code]
CREATE OR REPLACE FUNCTION "aprtr_geraemail_agcompagamento" ()    
RETURNS trigger AS
$BODY$
   
    DECLARE
    vSUBJECT varchar(500);
    vEMAIL_MSG_BRUTO text;
    vEMAIL_MSG_COMPOSTA text; -- Tem o body do email já preenchido.
    vEMAIL_TO varchar(500);
   
    -- Variaveis de configuração
    vID_EMAIL_MSG varchar(20);
    vEMAIL_FROM varchar(500);
    vMAX_TRIES int4;
   
   
    BEGIN
    -- ## CONFIGURACOES
    vID_EMAIL_MSG := 'ag_com_pag_sucesso'; -- campo id_email_msg
    vEMAIL_FROM := 'adefinir@mail.com';
    vMAX_TRIES := 3; -- Número máximo de vezes que a mensagem vai tentar ser enviada
    -- ##
   
    -- CONDICOES PARA REALIZAR AS TAREFAS
    if     new.id_estado_insercao = 'sucesso'
    and new.id_estado_concordancia_contrato = 'aceite'
    and new.id_estado_concordancia_pagamento = 'aceite'
    and new.id_estado_pagamento = 'pago' then
   
    -- Vou buscar a mensagem de email (ag_com_pag_sucesso)
    select email_subject, email_msg
    from
    aem_hist_mensagens_email
    where
    id_email_msg = vID_EMAIL_MSG
    and dat_fim is null
    into vSUBJECT, vEMAIL_MSG_BRUTO;
   
   
    -- Vou fazer a mensagem de email
    select
    replace(replace(replace(replace(replace(replace(vEMAIL_MSG_BRUTO, '@numero_anuncio@', a.id_anuncio_externo), '@nome_anuncio@', a.n_anuncio), '@idade@', EXTRACT(year from AGE(NOW(), a.dat_nasc))), '@telefone_anuncio@', a.telefone_anuncio), '@orientacao_sexual@', o.n_orientacao), '@cidade@', c.n_cidade) as email_composto,
    a.email as email_to
    -- a.id_anuncio_externo, a.n_anuncio, a.telefone_anuncio, a.dat_nasc,
    -- c.n_cidade, n.n_nacionalidade, o.n_orientacao
    from
    aae_anuncios a
    join aa_cidades c ON a.id_cidade = c.id_cidade
    join ae_nacionalidades n ON a.id_nacionalidade = n.id_nacionalidade
    join ae_orientacao o ON a.id_orientacao = o.id_orientacao
    where id_anuncio_externo = OLD.id_anuncio_externo
    into vEMAIL_MSG_COMPOSTA, vEMAIL_TO;
   
   
    -- Vou inserir na tabela de mensagens de email (atem_emails_envios)
    insert into aem_emails_envios
    (id_email_msg, dat_inserted, max_tries, email_from, email_to, email_subject, email_msg)
    values
    (vID_EMAIL_MSG, now(), vMAX_TRIES, vEMAIL_FROM, vEMAIL_TO, vSUBJECT, vEMAIL_MSG_COMPOSTA);
   
    -- DEBUG
    -- raise notice ' % ', vEMAIL_MSG_COMPOSTA;
    end if;
   
    RETURN NULL;
    END;
$BODY$
    LANGUAGE PLpgSQL
    CALLED ON NULL INPUT
    VOLATILE
    EXTERNAL SECURITY DEFINER;
[/code]

This code works great in my development machine in Windows. When I move this to the production machine gives me this error:


[error]
SQL Error:

ERROR:  function replace(text, unknown, integer) does not exist
LINE 1: select replace(replace(replace(replace(replace(replace( $1 ,...
                                                       ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  select replace(replace(replace(replace(replace(replace( $1 , '@numero_anuncio@', a.id_anuncio_externo), '@nome_anuncio@', a.n_anuncio), '@idade@', EXTRACT(year from AGE(NOW(), a.dat_nasc))), '@telefone_anuncio@', a.telefone_anuncio), '@orientacao_sexual@', o.n_orientacao), '@cidade@', c.n_cidade) as email_composto, a.email as email_to from aae_anuncios a join aa_cidades c ON a.id_cidade = c.id_cidade join ae_nacionalidades n ON a.id_nacionalidade = n.id_nacionalidade join ae_orientacao o ON a.id_orientacao = o.id_orientacao where id_anuncio_externo =  $2
CONTEXT:  PL/pgSQL function "aprtr_geraemail_agcompagamento" line 46 at SQL statement
[/error]

What can I do to correct this? Some clues?


Best Regards.

In response to Andre Lopes :
> Hi,
>
> I have a trigger that runs in my Development machine but not in my Production
> machine. the code is the following:
> SQL Error:
>
> ERROR:  function replace(text, unknown, integer) does not exist
> LINE 1: select replace(replace(replace(replace(replace(replace( $1 ,...
>                                                        ^

Let me guess: you have 8.2 on your Development machine and 8.3 or 8.4 on
your production machine, right?

I think you need to add explicit casts, let me explain:




> HINT:  No function matches the given name and argument types. You might need to
> add explicit type casts.
> QUERY:  select replace(replace(replace(replace(replace(replace( $1 ,
> '@numero_anuncio@', a.id_anuncio_externo), '@nome_anuncio@', a.n_anuncio),
> '@idade@', EXTRACT(year from AGE(NOW(), a.dat_nasc))), '@telefone_anuncio@',

EXTRACT(year ...) returns an INT, but replace(...) expects a TEXT or a
similar type. Try to add a ::TEXT after the EXTRACT(...) - function:

extract (year from ...)::text

Maybe there are more occurrences ...


Greetings from saxony, germany.
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

Thanks a lot, it works!

I'am using Postgres Plus Advanced Server 8.3R2 in development.In production I user PostreSQL 8.3.9.

Best Regards,

In response to Andre Lopes :
> Thanks a lot, it works!
>
> I'am using Postgres Plus Advanced Server 8.3R2 in development.In production I
> user PostreSQL 8.3.9.

Yeah, AFAIK is the "Postgres Plus Advanced Server" the version of the
regular PG-version plus 1. So you have 8.2 as development and 8.3 as
production version.


Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

On Wed, 2010-04-14 at 14:20 -0400, Scott Mead wrote:
>
> On Wed, Apr 14, 2010 at 1:43 PM, A. Kretschmer
> <andreas.kretschmer@schollglas.com> wrote:
>         In response to Andre Lopes :
>
>         > Thanks a lot, it works!
>         >
>         > I'am using Postgres Plus Advanced Server 8.3R2 in
>         development.In production I
>         > user PostreSQL 8.3.9.
>
>
>         Yeah, AFAIK is the "Postgres Plus Advanced Server" the version
>         of the
>         regular PG-version plus 1. So you have 8.2 as development and
>         8.3 as
>         production version.
>
>
> That's only true for the 8.2 series. :
>
>
> PPAS 8.1 = PostgreSQL 8.1
> PPAS 8.3x = PostgreSQL 8.2 base (plus HOT and wal_writer)
> PPAS 8.4 = PostgreSQL 8.4 (plus compatibility)
> PPAS 9 [is planned to] = PostgreSQL 9.0

So there was no actual 8.3 base release of PPAS?

Joshua D. Drake



--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering

On Wed, 2010-04-14 at 14:20 -0400, Scott Mead wrote:
>
> On Wed, Apr 14, 2010 at 1:43 PM, A. Kretschmer
> <andreas.kretschmer@schollglas.com> wrote:
>         In response to Andre Lopes :
>
>         > Thanks a lot, it works!
>         >
>         > I'am using Postgres Plus Advanced Server 8.3R2 in
>         development.In production I
>         > user PostreSQL 8.3.9.
>
>
>         Yeah, AFAIK is the "Postgres Plus Advanced Server" the version
>         of the
>         regular PG-version plus 1. So you have 8.2 as development and
>         8.3 as
>         production version.
>
>
> That's only true for the 8.2 series. :
>
>
> PPAS 8.1 = PostgreSQL 8.1
> PPAS 8.3x = PostgreSQL 8.2 base (plus HOT and wal_writer)
> PPAS 8.4 = PostgreSQL 8.4 (plus compatibility)
> PPAS 9 [is planned to] = PostgreSQL 9.0

So there was no actual 8.3 base release of PPAS?

Joshua D. Drake



--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering

Joshua D. Drake wrote:
> On Wed, 2010-04-14 at 14:20 -0400, Scott Mead wrote:
> >
> > On Wed, Apr 14, 2010 at 1:43 PM, A. Kretschmer
> > <andreas.kretschmer@schollglas.com> wrote:
> >         In response to Andre Lopes :
> >
> >         > Thanks a lot, it works!
> >         >
> >         > I'am using Postgres Plus Advanced Server 8.3R2 in
> >         development.In production I
> >         > user PostreSQL 8.3.9.
> >
> >
> >         Yeah, AFAIK is the "Postgres Plus Advanced Server" the version
> >         of the
> >         regular PG-version plus 1. So you have 8.2 as development and
> >         8.3 as
> >         production version.
> >
> >
> > That's only true for the 8.2 series. :
> >
> >
> > PPAS 8.1 = PostgreSQL 8.1
> > PPAS 8.3x = PostgreSQL 8.2 base (plus HOT and wal_writer)
> > PPAS 8.4 = PostgreSQL 8.4 (plus compatibility)
> > PPAS 9 [is planned to] = PostgreSQL 9.0
>
> So there was no actual 8.3 base release of PPAS?

No, and I assume the version numbers will all match for future releases.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

On Thu, Apr 15, 2010 at 4:10 AM, Bruce Momjian <bruce@momjian.us> wrote:
>> > PPAS 8.1 = PostgreSQL 8.1
>> > PPAS 8.3x = PostgreSQL 8.2 base (plus HOT and wal_writer)
>> > PPAS 8.4 = PostgreSQL 8.4 (plus compatibility)
>> > PPAS 9 [is planned to] = PostgreSQL 9.0
>>
>> So there was no actual 8.3 base release of PPAS?
>
> No, and I assume the version numbers will all match for future releases.

That's the plan.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company