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