Thread: 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.
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,
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.kretschmer@schollglas.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:Let me guess: you have 8.2 on your Development machine and 8.3 or 8.4 on
>
> ERROR: function replace(text, unknown, integer) does not exist
> LINE 1: select replace(replace(replace(replace(replace(replace( $1 ,...
> ^
your production machine, right?
I think you need to add explicit casts, let me explain:EXTRACT(year ...) returns an INT, but replace(...) expects a TEXT or a
> 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@',
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@postgresql.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.kretschmer@schollglas.com> wrote:
In response to Andre Lopes :> Thanks a lot, it works!Yeah, AFAIK is the "Postgres Plus Advanced Server" the version of the
>
> I'am using Postgres Plus Advanced Server 8.3R2 in development.In production I
> user PostreSQL 8.3.9.
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@postgresql.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.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