Skip site navigation (1) Skip section navigation (2)

createArrayOf, type resolved outside search_path on arrays of composite

From: "Jean-Pierre Pelletier" <jppelletier(at)e-djuster(dot)com>
To: <pgsql-jdbc(at)postgresql(dot)org>
Subject: createArrayOf, type resolved outside search_path on arrays of composite
Date: 2008-09-09 22:44:32
Message-ID: E80693D4966649D7A679B51CE3C68AC5@edjuster.network (view raw or flat)
Thread:
Lists: pgsql-jdbc
Hi,

I've got a composite type testtype and a function testfunction(testtype[]) 
of the same name in two schemas.
The function is called with callableStatement and the parameter set with 
createArrayOf() of jdbc 4.
The error received when calling the function: ERROR: function 
testfunction(testschema1.testtype[]) does not exist
suggests that the unqualified type is resolved to a type from a schema not 
in the search_path.

I've tried calling createArrayOf with a schema qualified type which gives 
this error:
Unable to find server array type for provided name testschema2.testtype

Here's the code to reproduce:

import java.io.*;
import java.sql.*;

public class Test_search_path {
   class testtype {
      Integer col1;

      /*
      * Override toString() because without this the PostgreSQL JDBC 4 
driver method createArrayOf()
      * have the PostgreSQL server throws "malformed record literal" with 
arrays of composite
      */
      public String toString() {
         StringBuffer sb = new StringBuffer("(");

         if (col1 != null) {
            org.postgresql.jdbc2.AbstractJdbc2Array.escapeArrayElement(sb, 
col1.toString());
         }

         sb = sb.append(")");

         return sb.toString();
      }
   }

   public static void main(String [ ] args)
   throws SQLException  {
      Connection        connection = null;
      CallableStatement cstmt      = null;
      Statement         stmt       = null;

      try {
         connection = DriverManager.getConnection("jdbc:postgresql:main", 
"postgres", null);
         stmt = connection.createStatement();
         connection.setAutoCommit(false);

         // set schema 1
         stmt.execute("CREATE SCHEMA testschema1");
         stmt.execute("CREATE TYPE testschema1.testtype AS (col1 integer)");
         stmt.execute("CREATE OR REPLACE FUNCTION 
testschema1.testfunction(testschema1.testtype []) RETURNS void AS $$ $$ 
LANGUAGE 'sql'");

         // set schema 2
         stmt.execute("CREATE SCHEMA testschema2");
         stmt.execute("CREATE TYPE testschema2.testtype AS (col1 integer)");
         stmt.execute("CREATE OR REPLACE FUNCTION 
testschema2.testfunction(testschema2.testtype []) RETURNS void AS $$ $$ 
LANGUAGE 'sql'");

         // call with schema 1, Ok
         stmt.execute("SET SEARCH_PATH = testschema1");
         cstmt = connection.prepareCall("{call testfunction(?) }");
         cstmt.setObject(1, connection.createArrayOf("testtype", new 
testtype[0]));
         cstmt.execute();

         // call with schema 2, error indicates "testschema1" which is not 
in Search Path
         stmt.execute("SET SEARCH_PATH = testschema2");
         cstmt = connection.prepareCall("{call testfunction(?) }");
         cstmt.setObject(1, connection.createArrayOf("testtype", new 
testtype[0]));
         cstmt.execute(); // ERROR: function 
testfunction(testschema1.testtype[]) does not exist

         // call with schema 2, error with schema qualified type name
         stmt.execute("SET SEARCH_PATH = testschema2");
         cstmt = connection.prepareCall("{call testfunction(?) }");
         cstmt.setObject(1, connection.createArrayOf("testschema2.testtype", 
new testtype[0]));
         cstmt.execute(); // Unable to find server array type for provided 
name testschema2.testtype
      }

      finally {
         if (connection != null) {
            connection.rollback(); // always rollback so we can repeat this 
test
         }

         if (stmt != null) {
            stmt.close();
         }

         if (cstmt != null) {
            cstmt.close();
         }

         if (connection != null) {
            connection.close();
         }
      }
   }
}


Responses

pgsql-jdbc by date

Next:From: Kris JurkaDate: 2008-09-09 23:24:04
Subject: Re: createArrayOf, type resolved outside search_path on arrays of composite
Previous:From: Daniel MigowskiDate: 2008-09-05 16:14:56
Subject: Re: Problem With Euro character

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group