Re: Patch: Auto-generate search_path statement for selected schema in query editor - Mailing list pgadmin-hackers

Hi all,

Recently I wrote a small patch for frmQuery.cpp in order to
auto-generate a "SET search_path TO ..." statement when opening the
query editor with a schema-related object selected in the object
browser, e.g. a table, function or sequence (or their respective
collections).
The reason being that in our company, after migrating from MSSQL Server
to pg, we use a single database for many of our customers, with one
schema per customer and identically named objects within those schemas.
E.g. cust_schema_01.table_x, cust_schema_02.table_x, cust_schema_03.table_x.
For maintenance and debugging, we connect to the database using the
postgres superuser account in pgAdmin3. Now in order to access table_x
within a certain customer's schema in the query editor, we always have
to prepend the schema name to the table name or issue a "SET search_path
TO cust_schema_nn,public" statement.
This is rather tedious, so I came up with a patch for pgAdmin3 that
tries to intelligently generate a search_path depending on the currently
selected object in the object browser as well as depending on the
existing search_path configured for the current database connection.
That way, we can easily open query editors under different schemas
without bothering about the search_path ourselves.

This is what my code does when opening a new query editor window:
- Check whether the currently selected object in the object browser is
of type pgSchema, pgSchemaObject or pgSchemaObjCollection or one of
their descendants which (if I'm not mistaken) means it does have a
schema associated with it.

- If so, it checks whether the schema belonging to this object is
already contained in the user's search_path (case-sensitively and
considering the $user placeholder).
- If the schema isn't already in the user's search_path, the code
generates a "SET search_path TO
<selected_schema>,<existing_search_path>" statement and has it written
into the newly opened query editor window.

- After that, it places the cursor to the end of the sql text so the
user can begin typing right away.

Tested on Windows XP Pro SP3 and Windows7 Pro only - I didn't bother to
create a build environment on my Linux box yet.
In case you deem this patch useful, find the diff output based on the
1.16.1 release source code attached below. There may occur usability
problems in combination with the existing "sticky SQL" option though. We
don't use the "sticky SQL" feature in our environment, so for now I
didn't spend too much thought on it.

Beware also that though being a developer, I'm really inexperienced in
C/C++ and completely new to wxWidgets and to the inner workings of
pgAdmin, so there may well be room for improvement in my code. Having
said that, I'd be willing to dig further into the pgAdmin3 code in order
to make this a configurable option, integrate it better into the
existing code etc. if need be.