current_query pg_stat_activity column

From: "Carlos H(dot) Reimer" <carlos(dot)reimer(at)opendb(dot)com(dot)br>
To: <pgsql-general(at)postgresql(dot)org>
Subject: current_query pg_stat_activity column
Date: 2008-02-22 14:50:51
Message-ID: PEEPKDFEHHEMKBBFPOOKKEDPGHAA.carlos.reimer@opendb.com.br
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I´ve noticed in my Fedora Core 6 box running PG 8.2.3 that column
current_query of pg_stat_activity view is not showing the complete query.

The complete query:
____________________________________________________________________________
_________________
SELECT INT.DESMAT, INT.ESPMAT, INT.MONTAR, INT.DECIMA, INT.REFBAS,
(SELECT DESMAR
FROM TT_MAR MAR
WHERE INT.FILMAR = MAR.FILMAR
AND INT.CODMAR = MAR.CODMAR
AND INT.ITEMAR = MAR.ITEMAR) AS DESMAR,
INT.DESCREVE,
CASE WHEN (INT.PROMOC <> '0') AND (INT.PROMOC <> 'Ver Grade')
THEN INT.PROMOC
ELSE CASE WHEN INT.CODGRA = 0 THEN (SELECT
MIN(REPLACE(REPLACE(REPLACE(TO_CHAR(PRE.PRECOV,'9,999,9990.99'),',','X'),'.'
,','),'X','.'))
FROM TT_PRE PRE
WHERE PRE.FILPRE =
'001'
AND PRE.CODPRE =
'1'
AND PRE.FILMAT =
INT.FILMAT
AND PRE.CODMAT =
INT.CODMAT)
ELSE 'Ver Grade'
END
END AS PRECO,
CASE WHEN INT.PROMOC = 'Ver Grade' THEN 'Ver Grade'
WHEN INT.PROMOC = '0' THEN 'Não' ELSE
'Sim' END AS PROMOC FROM (
SELECT PRO.DESMAT, PRO.ESPMAT, PRO.MONTAR, PRO.DECIMA, PRO.REFBAS,
PRO.FILMAR, PRO.CODMAR, PRO.ITEMAR,
PRO.FILMAT,PRO.CODMAT,PRO.CODGRA,
COALESCE(RPad(PRO.DESMAT,30),'') ||
COALESCE(RPad(PRO.ESPMAT,30),'') || COALESCE(RPad(PRO.REFBAS,15),'') AS
DESCREVE,
COALESCE(CASE WHEN PRO.CODGRA = 0 THEN (SELECT
MIN(REPLACE(REPLACE(REPLACE(TO_CHAR(CPM.PRECOP,'9,999,999.99'),',','X'),'.',
','),'X','.'))
FROM TT_CPM CPM INNER JOIN
TT_PRE PRE ON CPM.FILPRE=PRE.FILPRE

AND CPM.SEQPRE=PRE.SEQUEN

AND COALESCE(CPM.FILPRO,'003') = '003'
WHERE PRE.FILMAT =
PRO.FILMAT
AND PRE.CODMAT =
PRO.CODMAT
AND PRE.FILPRE = '001'
AND PRE.CODPRE = '1'
AND CPM.DATVAL >=
TRUNC( AGORA())
AND (( CPM.FILPGT = '001'
AND CPM.CODPGT = ' 1') OR (CPM.FILPGT IS NULL))
) ELSE 'Ver Grade' END,'0') AS
PROMOC
FROM TT_PRO PRO
WHERE PRO.SIGNAT >= 0
AND PRO.FILMAT = PRO.FILMAT
AND PRO.CODMAT = PRO.CODMAT
AND EXISTS (SELECT 1 AS OK FROM TT_GRA GRA WHERE GRA.FILMAT =
PRO.FILMAT AND GRA.CODMAT = PRO.CODMAT AND GRA.GRAATI = 'T')
ORDER BY PRO.DESMAT, PRO.ESPMAT, PRO.REFBAS
) INT

____________________________________________________________________________
_________________

The output from the select current_query from pg_stat_activity:
____________________________________________________________________________
_________________
SELECT INT.DESMAT, INT.ESPMAT, INT.MONTAR, INT.DECIMA, INT.REFBAS,
(SELECT DESMAR
FROM TT_MAR MAR
WHERE INT.FILMAR = MAR.FILMAR
AND INT.CODMAR = MAR.CODMAR
AND INT.ITEMAR = MAR.ITEMAR) AS DESMAR,
INT.DESCREVE,
CASE WHEN (INT.PROMOC <> '0') AND (INT.PROMOC <> 'Ver Grade')
THEN INT.PROMOC
ELSE CASE WHEN INT.CODGRA = 0 THEN (SELECT
MIN(REPLACE(REPLACE(REPLACE(TO_CHAR(PRE.PRECOV,'9,999,9990.99'),',','X
'),'.',','),'X','.'))
FROM TT_PRE PRE
WHERE PRE.FILPRE = '001'
AND PRE.CODPRE = '2'
AND PRE.FILMAT =
INT.FILMAT
AND PRE.CODMAT =
INT.CODMAT)
ELSE 'Ver Grade'
END
END AS PRECO,
CASE WHEN INT.PROMOC = 'Ver Grade' THEN '

____________________________________________________________________________
_________________

Am I missing anything here?

Thank you in advance!

Reimer

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2008-02-22 14:58:56 Re: Unique indicies
Previous Message Dot Yet 2008-02-22 14:38:28 Re: Unique indicies