Re: pgadmin III query - Mailing list pgsql-general

From Dinesh Kumar
Subject Re: pgadmin III query
Date
Msg-id CAKWsr7g9y7oUqBjum=mpSMH+3mDWk1Dh9LSye+3g9ghU_hZj2w@mail.gmail.com
Whole thread Raw
In response to Re: pgadmin III query  (Peter Kroon <plakroon@gmail.com>)
List pgsql-general
Hi Peter,

On Mon, Dec 9, 2013 at 7:52 PM, Peter Kroon <plakroon@gmail.com> 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@enterprisedb.com>
Hi Peter,

On Mon, Dec 9, 2013 at 7:03 PM, Peter Kroon <plakroon@gmail.com> 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@enterprisedb.com>
Hi,

On Fri, Dec 6, 2013 at 4:34 PM, Peter Kroon <plakroon@gmail.com> 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@gmail.com>
2013/12/6 Peter Kroon <plakroon@gmail.com>:
> 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






pgsql-general by date:

Previous
From: John R Pierce
Date:
Subject: Re: [pgadmin-support] Lost database
Next
From: Yuriy Rusinov
Date:
Subject: Return setof values from C-function