Re: pgadmin III query

[Dinesh Kumar <dinesh.kumar@xxxxxxxxxxxxxxxx>]

Hi Peter,

On Mon, Dec 9, 2013 at 7:52 PM, Peter Kroon <plakroon@xxxxxxxxx> wrote:

Hi Dinesh,

SELECT pg_reload_conf();

Did not do the job, I had to restart the server.

I managed to collect the queries and there are a lot of them to show the SQL that is needed to create the given table.

Does postrgesql have any plan on making their own function for this?

I am not the right person to answer this question. Hope some will give you answer for this.

I am not sure whether it works for you or not.

Regards,
Dinesh

Best,
Peter

 

2013/12/9 Dinesh Kumar <dinesh.kumar@xxxxxxxxxxxxxxxx>

Hi Peter,

On Mon, Dec 9, 2013 at 7:03 PM, Peter Kroon <plakroon@xxxxxxxxx> wrote:

Hi Dinesh,

>Get all the queries what it has performed.

How and where?
When I run "select * from pg_stat_activity" I get the same result with and without "log_minduration_statement=0"

By setting this parameter log_min_duration_statement to 0, postgres will log all the queries, in the pg_log file.

Hope the following steps helps you on this, and make sure you have enabled the logging_collector.

1. Modify the above parameter on the required postgres cluster.

2. Do SELECT pg_reload_conf(); on the same machine.

3. And go to pg_log file location, and do tail -f current pg_log file.

4. Go to pgadmin, and refresh on any table.

5. Check the tail -f <pg_log>file output. There you will find all the sql queries, which have been executed from pgAdmin.

6. Collect those queries, and make your own custom function with pl/pgsql language.

Regards,

Dinesh

 

Could you provide a more detailed step by step guide?

Best,

Peter

2013/12/9 Dinesh Kumar <dinesh.kumar@xxxxxxxxxxxxxxxx>

Hi,

On Fri, Dec 6, 2013 at 4:34 PM, Peter Kroon <plakroon@xxxxxxxxx> wrote:

Thanks, but i need a non command line option.

We can do this with a function which is having the sql queries of pgAdmin raised against the database.

=> Log all the queries by enabling "log_minduration_statement=0".

=> Do SELECT pg_reload_conf();

=> Do a refresh on a table of pgAdmin's browser.

=> Get all the queries what it has performed.

=> Create a custom function with those queries.

Regards,

Dinesh

 

2013/12/6 Ian Lawrence Barwick <barwick@xxxxxxxxx>

2013/12/6 Peter Kroon <plakroon@xxxxxxxxx>:

> When you click on a table in the "Object browser" you'll see in the "SQL
> pane" the sql that is needed to create that table.
>
> Which function can I call to get that SQL?

You can use the pg_dump command line function for this:

  pg_dump -s -t name_of_table name_of_database

Regards

Ian Barwick