Patch to allow setting schema/search_path in the connectionURL

[Scott Langley-3]

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@...


--- 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();


--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

[Heikki Linnakangas-2]

Scott Langley wrote:

> 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 think it would be more useful to provide a way to set any GUC variable
in the connection string, not just search_path.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

[Kris Jurka]

On Wed, 9 Jul 2008, Heikki Linnakangas wrote:

> I think it would be more useful to provide a way to set any GUC variable in
> the connection string, not just search_path.
>

In previous discussions:

http://archives.postgresql.org/pgsql-jdbc/2004-02/thrd2.php#00022

One significant roadblock was how to handle the Datasource implementation
which needs to know all the available GUC parameters at compile time:

http://archives.postgresql.org/pgsql-jdbc/2004-02/msg00037.php

I'm not particularly excited about enumerating them all and the suggested
alternative methods of passing these values in Properties, String[][]
don't seem terribly appealing

Kris Jurka

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc