Re: Multiple Functions and Tables in different Schemas with COmposite Types

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

}

On Thu, Mar 11, 2010 at 5:39 PM, Jason Tesser <jasontesser(at)gmail(dot)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(at)gmail(dot)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(at)gmail(dot)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.
>>>
>>>
>>
>