Patch to allow setting schema/search_path in the connectionURL

[Scott Langley <slangley(at)scharp(dot)org>]

Many years ago, Andreas Joseph Krogh submitted a patch to allow the 
setting of the default schema in a JDBC connection string:

  http://archives.postgresql.org/pgsql-jdbc/2002-12/msg00187.php

It was rejected with some objections:
  http://archives.postgresql.org/pgsql-jdbc/2002-12/msg00189.php

I've made a modern version of this patch against the current CVS head 
that hopefully addresses the earlier objections on this list.  In this 
version, however, the parameter is called 'searchpath' instead of 'schema'

I find this functionality useful for a data reporting tool like BIRT:
  http://www.eclipse.org/birt/phoenix/
that expects you to provide the JDBC connection parameters but where the 
 tool will build appropriate SQL queries for report generation - based 
on your manipulation of the report designer interface.  Our DBA likes to 
use postgresql schemas for namespace  management. Setting a searchpath 
keyword in the JDBC connection parameters allows the BIRT reports to be 
easily portable for use with differently-named schemas.

E.g.:

	jdbc:postgresql://localhost:5432/mydatabase?searchpath=myschema

Incidentally, I noticed that the npgsql developers added similar 
functionality to their Posgresql driver in November of last year:

  http://archives.postgresql.org/pgsql-committers/2007-11/msg00506.php

Regards,

Scott Langley
--
Systems Analyst/Programmer
Statistical Center for HIV/AIDS Research and Prevention (SCHARP)
Fred Hutchinson Cancer Research Center
Seattle, Washington
slangley(at)scharp(dot)org

--- pgjdbc/doc/pgjdbc.xml	Sun Apr 13 09:03:49 2008
+++ pgjdbc_searchpath/doc/pgjdbc.xml	Tue Jul  8 11:13:02 2008
@@ -525,6 +525,18 @@
       </varlistentry>
 
       <varlistentry>
+       <term><varname>searchpath</varname> = <type>String</type></term>
+       <listitem>
+        <para>
+		Set a custom search path for this connection.  The first schema in the path will be 
+		used for any tables or other named objects that are created when no target schema
+		is specified in a command. This is only useful in the situation where it cannot be 
+		handled by the per user or per database defaults. 		
+        </para>
+       </listitem>
+      </varlistentry>
+      
+      <varlistentry>
        <term><varname>allowEncodingChanges</varname> = <type>boolean</type></term>
        <listitem>
         <para>
--- pgjdbc/org/postgresql/Driver.java.in	Sun Apr 13 09:03:49 2008
+++ pgjdbc_searchpath/org/postgresql/Driver.java.in	Tue Jul  8 10:00:56 2008
@@ -180,6 +180,8 @@
      *  server then the character set of the database is used as the default,
      *  otherwise the jvm character encoding is used as the default.
      *   This value is only used when connecting to a 7.2 or older server.
+     * searchpath - (optional) Set a custom search path. Useful if you want to 
+     *  query a non-default schema but don't wish to modify your queries. 
      * loglevel - (optional) Enable logging of messages from the driver.
      *  The value is an integer from 1 to 2 where:
      *    INFO = 1, DEBUG = 2
@@ -447,6 +449,8 @@
                 { "stringtype", Boolean.FALSE,
                   "The type to bind String parameters as (usually 'varchar'; 'unspecified' allows implicit casting to other types)",
                   new String[] { "varchar", "unspecified" } },
+                { "searchpath", Boolean.FALSE,
+                  "Set a custom search path." },
             };
 
     /**
--- pgjdbc/org/postgresql/jdbc2/AbstractJdbc2Connection.java	Mon Apr 14 21:23:57 2008
+++ pgjdbc_searchpath/org/postgresql/jdbc2/AbstractJdbc2Connection.java	Tue Jul  8 13:13:40 2008
@@ -160,6 +160,13 @@
         _typeCache = createTypeInfo(this);
         initObjectTypes(info);
 
+        // Set Custom Search Path
+        String searchpath = info.getProperty("searchpath");
+        if((searchpath != null) && (!(searchpath.trim().isEmpty()))){
+        	String set_cmd = "SET search_path TO " + searchpath + "; show search_path";
+        	execSQLQuery(set_cmd);
+        }
+        
         if (Boolean.valueOf(info.getProperty("logUnclosedConnections")).booleanValue()) {
             openStackTrace = new Throwable("Connection was created at this point:");
             enableDriverManagerLogging();