Thread: retrieve statement from catalogs

Hi everybody, can anyone tell me if there's a way to retrieve the select
instruction executed from the catalogs, or maybe via some structure in a
trigger?

The reason is that i have some selects constructed on-the-fly (just part of
it) and i want to save that in a table in order to know what are the most
used for optimizing them.

Another reason for doing that is that i don't know all the selects in the
program nor the page and this is an easier solution than examine all the
code.

thanx in advance, el_vigia

_________________________________________________________________
STOP MORE SPAM with the new MSN 8 and get 2 months FREE*
http://join.msn.com/?page=features/junkmail

On Tue, Oct 28, 2003 at 09:03:12AM +0000, Jaime Casanova wrote:
>
> Hi everybody, can anyone tell me if there's a way to retrieve the select
> instruction executed from the catalogs, or maybe via some structure in a
> trigger?

You could turn on query logging and read the Postgres logs.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Afilias Canada                        Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

On Tuesday 28 October 2003 14:33, Jaime Casanova wrote:
> Hi everybody, can anyone tell me if there's a way to retrieve the select
> instruction executed from the catalogs, or maybe via some structure in a
> trigger?
>
> The reason is that i have some selects constructed on-the-fly (just part of
> it) and i want to save that in a table in order to know what are the most
> used for optimizing them.
>
> Another reason for doing that is that i don't know all the selects in the
> program nor the page and this is an easier solution than examine all the
> code.

Use pg_ctl -l logfile option to start postmaster. It will log the things. And
set log_statement=true in postgresql.conf.

So there will be all the sql statements logged in.

 HTH

 Shridhar

What a solution!!!

I need to insert the querys in a table

_________________________________________________________________
STOP MORE SPAM with the new MSN 8 and get 2 months FREE*
http://join.msn.com/?page=features/junkmail

Hi Jaime,

Jaime Casanova wrote:
> What a solution!!!
>
> I need to insert the querys in a table
>
Why?
And what about using the logfile for anylysis?
And if you really want a custom log of your
generated queries - why not just insert your
query literals after generation in your table?
Of course, it can be a big resource hog, but
you apparently want it :-)

Regards
Tino Wildenhain

STOP MORE SPAM with the new MSN 8 and get 2 months FREE*