> > Envie o conteudo de blah blah blah para analisarmos, deve ser nele.
> > Um sugestão, faça a mesma chamada que a aplicação faz pelo pgAdmin, a
> > mensagem de erro traz mais algumas informações uteis, como o número da
> > linha que deu o problema. Isso pode te ajudar. Ai você só envia a linha
> > com erro.
> >
> > Evandro
>
> Evandro,
> No pgAdmin a chamada funciona!
> Mais diferenças: a versão do driver JDBC!
> No ambiente onde não funciona, estou usando a versão 8.3-603 JDBC 3.
> No ambiente onde funciona, uso alguma versão 8.1 ou 8.2. Sempre JDBC 3.
> Vou testar com outro driver e posto o resultado.
Testei com os seguintes drivers:
postgresql-8.0-310.jdbc3.jar
postgresql-8.1-412.jdbc3.jar
postgresql-8.2-508.jdbc3.jar
postgresql-8.3-603.jdbc3.jar
Todos apresentam a mesma mensagem de erro.
Segue a function completa...
-----------------------------------------
CREATE OR REPLACE FUNCTION message_sms_mt_consolidate(data_limite timestamp
without time zone)
RETURNS integer AS
$BODY$
DECLARE
mtrp RECORD;
mtrp_found INTEGER;
BEGIN
-- Insere na tabela temporaria todas as mensagens que serao
arquivadas
EXECUTE '
CREATE TEMP TABLE message_sms_mt_tmp AS
SELECT
smst_id,
msmt_id,
pers_id,
smst_status,
smst_error,
smst_scheduled,
smst_sent,
smst_status_update,
smst_mobile,
smst_from,
smst_body,
acco_id,
sect_id,
smst_external_key
FROM message_sms_mt
WHERE smst_scheduled < ' || quote_literal($1) || '
AND smst_status > 3;';
-- Exclui da tabela quente todas as mensagens que serao arquivadas
EXECUTE '
DELETE
FROM message_sms_mt
WHERE smst_id IN (
SELECT smst_id
FROM message_sms_mt_tmp)';
-- Insere na tabela de historico todas as msgs da tabela temporaria
EXECUTE '
INSERT INTO message_sms_mt_history (
smst_id,
msmt_id,
pers_id,
smst_status,
smst_error,
smst_scheduled,
smst_sent,
smst_status_update,
smst_mobile,
smst_from,
smst_body,
acco_id,
sect_id,
smst_external_key)
SELECT
smst_id,
msmt_id,
pers_id,
smst_status,
smst_error,
smst_scheduled,
smst_sent,
smst_status_update,
smst_mobile,
smst_from,
smst_body,
acco_id,
sect_id,
smst_external_key
FROM message_sms_mt_tmp;';
-- Verifica todas as mensagens da tabela temporaria
FOR mtrp IN EXECUTE '
SELECT available.acco_id AS acco_id,
available.date AS mtrp_date,
coalesce(received_by_mobile.total,0) AS
received_by_mobile,
coalesce(received_by_mobile_unavailable.total,0) AS
received_by_mobile_unavailable,
coalesce(error.total,0) AS error,
coalesce(blocked.total,0) AS blocked
FROM (
SELECT acco_id, cast(smst_scheduled AS date) AS date
FROM message_sms_mt_tmp
WHERE smst_status > 3
AND acco_id IS NOT NULL
AND smst_mobile NOT LIKE ' ||
quote_literal('5500%') || '
GROUP BY acco_id, date
ORDER BY acco_id ASC, date DESC
) AS available
FULL OUTER JOIN (
SELECT COUNT(*) AS total, acco_id,
cast(smst_scheduled AS date) AS date
FROM message_sms_mt_tmp
WHERE smst_status = 4
AND acco_id IS NOT NULL
AND smst_mobile NOT LIKE ' ||
quote_literal('5500%') || '
GROUP BY acco_id, date
ORDER BY acco_id ASC, date DESC
) AS received_by_mobile
ON available.acco_id = received_by_mobile.acco_id AND
available.date = received_by_mobile.date
FULL OUTER JOIN (
SELECT COUNT(*) AS total, acco_id,
cast(smst_scheduled AS date) AS date
FROM message_sms_mt_tmp
WHERE smst_status = 6
AND acco_id IS NOT NULL
AND smst_mobile NOT LIKE ' ||
quote_literal('5500%') || '
GROUP BY acco_id, date
ORDER BY acco_id ASC, date DESC
) AS received_by_mobile_unavailable
ON available.acco_id =
received_by_mobile_unavailable.acco_id AND available.date =
received_by_mobile_unavailable.date
FULL OUTER JOIN (
SELECT COUNT(*) AS total, acco_id,
cast(smst_scheduled AS date) AS date
FROM message_sms_mt_tmp
WHERE smst_status = 5
AND acco_id IS NOT NULL
AND smst_mobile NOT LIKE ' ||
quote_literal('5500%') || '
GROUP BY acco_id, date
ORDER BY acco_id ASC, date DESC
) AS error
ON available.acco_id = error.acco_id AND available.date =
error.date
FULL OUTER JOIN (
SELECT COUNT(*) AS total, acco_id,
cast(smst_scheduled AS date) AS date
FROM message_sms_mt_tmp
WHERE smst_status = 10
AND acco_id IS NOT NULL
AND smst_mobile NOT LIKE ' ||
quote_literal('5500%') || '
GROUP BY acco_id, date
ORDER BY acco_id ASC, date DESC
) AS blocked
ON available.acco_id = blocked.acco_id AND available.date =
blocked.date
ORDER BY acco_id, mtrp_date'
LOOP
IF (mtrp.acco_id IS NOT NULL AND mtrp.mtrp_date IS NOT NULL)
THEN
RAISE NOTICE 'acco_id: % - %',mtrp.acco_id,mtrp.mtrp_date;
EXECUTE ' SELECT COUNT(*) FROM message_sms_mt_report WHERE
acco_id = '
|| mtrp.acco_id
|| ' AND mtrp_date LIKE '
|| quote_literal(mtrp.mtrp_date)
INTO mtrp_found;
RAISE NOTICE 'count: %',mtrp_found;
IF mtrp_found = 0 THEN
--RAISE NOTICE 'Not found...';
EXECUTE 'INSERT INTO message_sms_mt_report ('
|| 'acco_id,'
|| 'mtrp_date,'
|| 'mtrp_received_by_mobile,'
|| 'mtrp_received_by_mobile_unavailable,'
|| 'mtrp_error,'
|| 'mtrp_blocked'
|| ') VALUES ('
|| mtrp.acco_id || ','
|| quote_literal(mtrp.mtrp_date) || ','
|| mtrp.received_by_mobile || ','
|| mtrp.received_by_mobile_unavailable ||
','
|| mtrp.error || ','
|| mtrp.blocked
|| ')';
ELSE
--RAISE NOTICE 'Found!!!';
EXECUTE 'UPDATE message_sms_mt_report SET '
|| ' mtrp_received_by_mobile =
mtrp_received_by_mobile + ' || mtrp.received_by_mobile
|| ',mtrp_received_by_mobile_unavailable =
mtrp_received_by_mobile_unavailable + ' ||
mtrp.received_by_mobile_unavailable
|| ',mtrp_error = mtrp_error + ' ||
mtrp.error
|| ',mtrp_blocked = mtrp_blocked + ' ||
mtrp.blocked
|| ' WHERE '
|| ' acco_id = ' || mtrp.acco_id
|| ' AND mtrp_date LIKE ' ||
quote_literal(mtrp.mtrp_date);
END IF;
END IF;
END LOOP;
-- Apaga a tabela temporaria (caso encontre)
EXECUTE 'DELETE FROM message_sms_mt_tmp';
EXECUTE 'DROP TABLE message_sms_mt_tmp';
RETURN 0;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
-----------------------------------------
Eduardo Bobsin Machado
________________________________
Human Mobile - Torpedos SMS
www.human.com.br
mail: [EMAIL PROTECTED]
phone: 51 3346 8282
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
