Обсуждение: Trouble Accessing Schema-Qualified Table
Hello,
I need to access a database using two different schemas. During initialization the default schema is set as follows:
set schema 'ucm';
Then later, I need to get data from a table in another schema 'ts_sofia_internal', so I tried the following, but it returns an error as shown:
teo=# select * from sip_presence('ts_sofia_internal') where sip_presence.sip_user='1003';
ERROR: function sip_presence(unknown) does not exist
LINE 1: select * from sip_presence('ts_sofia_internal') where sip_pr...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
teo=#
Does anyone know how I can do this? By the way, I'm using odbc to connect to the database.
Thanks,
Jerry
On Mon, Nov 15, 2010 at 8:35 AM, Jerry Richards
<jerry.richards@teotech.com> wrote:
>
> teo=# select * from sip_presence('ts_sofia_internal') where
> sip_presence.sip_user='1003';
>
> ERROR: function sip_presence(unknown) does not exist
>
> LINE 1: select * from sip_presence('ts_sofia_internal') where sip_pr...
> ^
> HINT: No function matches the given name and argument types. You might need
> to add explicit type casts.
The problem isn't the schema name, it is with the parameter you are
using in your function. Since you don't show the definition of your
function, I'm going to take a guess and assume your input
'ts_sofia_internal' is a TEXT type, so you might do something like:
select * from sip_presence('ts_sofia_internal'::TEXT) where ...
If not "TEXT" type, check your function to see what it expects the type to be
\df sip_presence
bricklen <bricklen@gmail.com> writes:
> On Mon, Nov 15, 2010 at 8:35 AM, Jerry Richards
> <jerry.richards@teotech.com> wrote:
>>
>> teo=# select * from sip_presence('ts_sofia_internal') where
>> sip_presence.sip_user='1003';
>>
>> ERROR: function sip_presence(unknown) does not exist
> The problem isn't the schema name, it is with the parameter you are
> using in your function.
While Jerry didn't say so in so many words, I suppose that the query
worked for him before he changed the search path. In that case the
problem *is* the schema name: his function is in a schema that is no
longer in the search path. He can either qualify the function name:
select * from other_schema.sip_presence('ts_sofia_internal') where
or list both schemas in his search_path so that the function can still
be found without a schema name attached.
regards, tom lane