Patch to allow setting schema/search_path in the connectionURL - Mailing list pgsql-jdbc

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@scharp.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
othertypes)", 
                   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();