Re: 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(at)mail(dot)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,

On Wed, Apr 14, 2010 at 2:19 PM, A. Kretschmer <
andreas(dot)kretschmer(at)schollglas(dot)com> wrote:

> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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, Apr 14, 2010 at 1:43 PM, A. Kretschmer <
andreas(dot)kretschmer(at)schollglas(dot)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

--Scott

>
>
> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

On Wed, 2010-04-14 at 14:20 -0400, Scott Mead wrote:
>
> On Wed, Apr 14, 2010 at 1:43 PM, A. Kretschmer
> <andreas(dot)kretschmer(at)schollglas(dot)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(dot)kretschmer(at)schollglas(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

On Thu, Apr 15, 2010 at 4:10 AM, Bruce Momjian <bruce(at)momjian(dot)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