Thread: Multiple Functions and Tables in different Schemas with COmposite Types
I am getting this error function person_save(testclient.person, unknown, testclient.email[], unknown) does not exist when using a prepared statement.
the reason for this is SELECT oid FROM pg_catalog.pg_type WHERE typname = $1
is called and $1 is person
it gets the wrong oid, multiple OID are returned
I tried to make my type be schema.person but that doesn't work
This IN parameters are composite types so I had to create objects that extended PGObject to make it work
My procs all work as I can call from pgadmin by setting searchpath. But from java with a prepared statement and ? as the IN parameters it fails even with the searchpath set.
the reason for this is SELECT oid FROM pg_catalog.pg_type WHERE typname = $1
is called and $1 is person
it gets the wrong oid, multiple OID are returned
I tried to make my type be schema.person but that doesn't work
This IN parameters are composite types so I had to create objects that extended PGObject to make it work
My procs all work as I can call from pgadmin by setting searchpath. But from java with a prepared statement and ? as the IN parameters it fails even with the searchpath set.
I guess my issues here is that the driver doesn't seem to provide me a way of handling tablenamespace when types of the same name live in 2 different schemas.
Can I control this at all?
Can I control this at all?
On Wed, Mar 10, 2010 at 9:07 PM, Jason Tesser <jasontesser@gmail.com> wrote:
I am getting this error function person_save(testclient.person, unknown, testclient.email[], unknown) does not exist when using a prepared statement.
the reason for this is SELECT oid FROM pg_catalog.pg_type WHERE typname = $1
is called and $1 is person
it gets the wrong oid, multiple OID are returned
I tried to make my type be schema.person but that doesn't work
This IN parameters are composite types so I had to create objects that extended PGObject to make it work
My procs all work as I can call from pgadmin by setting searchpath. But from java with a prepared statement and ? as the IN parameters it fails even with the searchpath set.
Any ideas here? I am pretty sure this is a bug.
It is easy to reproduce.
1. Create 2 schemas with tables of the same name
2. Create the same function in each schema each taking as an IN param the table
3. From Java use a Stored proc to call the function setting the search_path accordingly.
4. Use a prepared statement to set the IN param
When it tries to set the parameter on the Prepared Statement one schema will work and the other will not.
It is easy to reproduce.
1. Create 2 schemas with tables of the same name
2. Create the same function in each schema each taking as an IN param the table
3. From Java use a Stored proc to call the function setting the search_path accordingly.
4. Use a prepared statement to set the IN param
When it tries to set the parameter on the Prepared Statement one schema will work and the other will not.
On Wed, Mar 10, 2010 at 9:20 PM, Jason Tesser <jasontesser@gmail.com> wrote:
I guess my issues here is that the driver doesn't seem to provide me a way of handling tablenamespace when types of the same name live in 2 different schemas.
Can I control this at all?On Wed, Mar 10, 2010 at 9:07 PM, Jason Tesser <jasontesser@gmail.com> wrote:I am getting this error function person_save(testclient.person, unknown, testclient.email[], unknown) does not exist when using a prepared statement.
the reason for this is SELECT oid FROM pg_catalog.pg_type WHERE typname = $1
is called and $1 is person
it gets the wrong oid, multiple OID are returned
I tried to make my type be schema.person but that doesn't work
This IN parameters are composite types so I had to create objects that extended PGObject to make it work
My procs all work as I can call from pgadmin by setting searchpath. But from java with a prepared statement and ? as the IN parameters it fails even with the searchpath set.
Sorry one more thing you need to create your table as a type extending PG_Object
like so
public class PGPerson extends PGobject implements Serializable, Cloneable{
/**
*
*/
private static final long serialVersionUID = 2347713583412585369L;
private Person person;
public PGPerson() {
setType("person");
}
public PGPerson(Person person){
this();
this.person = person;
}
@Override
public String getValue() {
return "(" + PGHelper.escapeObjectForPostgres(person.getId() + "") + "," + PGHelper.escapeObjectForPostgres(person.getFirstName()) + "," + PGHelper.escapeObjectForPostgres(person.getLastName()) + "," + PGHelper.escapeObjectForPostgres(person.getMiddleName()) + ")";
}
}
like so
public class PGPerson extends PGobject implements Serializable, Cloneable{
/**
*
*/
private static final long serialVersionUID = 2347713583412585369L;
private Person person;
public PGPerson() {
setType("person");
}
public PGPerson(Person person){
this();
this.person = person;
}
@Override
public String getValue() {
return "(" + PGHelper.escapeObjectForPostgres(person.getId() + "") + "," + PGHelper.escapeObjectForPostgres(person.getFirstName()) + "," + PGHelper.escapeObjectForPostgres(person.getLastName()) + "," + PGHelper.escapeObjectForPostgres(person.getMiddleName()) + ")";
}
}
On Thu, Mar 11, 2010 at 5:39 PM, Jason Tesser <jasontesser@gmail.com> wrote:
Any ideas here? I am pretty sure this is a bug.
It is easy to reproduce.
1. Create 2 schemas with tables of the same name
2. Create the same function in each schema each taking as an IN param the table
3. From Java use a Stored proc to call the function setting the search_path accordingly.
4. Use a prepared statement to set the IN param
When it tries to set the parameter on the Prepared Statement one schema will work and the other will not.On Wed, Mar 10, 2010 at 9:20 PM, Jason Tesser <jasontesser@gmail.com> wrote:I guess my issues here is that the driver doesn't seem to provide me a way of handling tablenamespace when types of the same name live in 2 different schemas.
Can I control this at all?On Wed, Mar 10, 2010 at 9:07 PM, Jason Tesser <jasontesser@gmail.com> wrote:I am getting this error function person_save(testclient.person, unknown, testclient.email[], unknown) does not exist when using a prepared statement.
the reason for this is SELECT oid FROM pg_catalog.pg_type WHERE typname = $1
is called and $1 is person
it gets the wrong oid, multiple OID are returned
I tried to make my type be schema.person but that doesn't work
This IN parameters are composite types so I had to create objects that extended PGObject to make it work
My procs all work as I can call from pgadmin by setting searchpath. But from java with a prepared statement and ? as the IN parameters it fails even with the searchpath set.