current_query pg_stat_activity column - Mailing list pgsql-general
| From | Carlos H. Reimer |
|---|---|
| Subject | current_query pg_stat_activity column |
| Date | |
| Msg-id | PEEPKDFEHHEMKBBFPOOKKEDPGHAA.carlos.reimer@opendb.com.br Whole thread Raw |
| Responses |
Re: current_query pg_stat_activity column
|
| List | 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
(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 '
(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
pgsql-general by date: