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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-jdbc by date

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