Re: BUG #15057: Issue with UNKNOW type when calling a PostgreSQL function from java code

Hi Pavel,

I agree with it,

But, We are running our query on different other databases like Oracle and
MySql where they doensn't need *::text *to append to define a text or
character or vachar type,
The same query is being executed on Oracle and MySql fine but PostgreSQL
treating it as unknown.

Another use case,

Here I just queried an integer without specifying ::integet ,but how could
pg_typeof know that is an integer. ?
[image: Inline image 1]

Thanks,
Praveen

On Sat, Feb 10, 2018 at 2:09 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

>
>
> 2018-02-10 9:22 GMT+01:00 Praveen Kumar <praveenkumar52028(at)gmail(dot)com>:
>
>> Hi Pavel,
>> Thank you for quick answer,
>>
>> This is a PostgreSQL bug, I tried this without orafce plugin, still the
>> same error ,Let me know if you want me to share screenshots.
>>
>
> No, it is usual behave - you have to fix (change) your application. String
> literal in PostgreSQL is not varchar or text by default. Without context
> info, it is unknown.
>
> Regards
>
> Pavel
>
> omega=# select pg_typeof('aaaa'::text);
> +-----------+
> | pg_typeof |
> +-----------+
> | text |
> +-----------+
> (1 row)
>
> omega=# select pg_typeof('aaaa');
> +-----------+
> | pg_typeof |
> +-----------+
> | unknown |
> +-----------+
> (1 row)
>
> it 100% correct
>
>
>
>
>
>
>>
>> Thanks,
>> Praveen
>>
>> On Sat, Feb 10, 2018 at 1:44 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
>> wrote:
>>
>>> Hi
>>>
>>> this is not PostgreSQL bug
>>>
>>> 2018-02-10 8:57 GMT+01:00 PG Bug reporting form <noreply(at)postgresql(dot)org>
>>> :
>>>
>>>> The following bug has been logged on the website:
>>>>
>>>> Bug reference: 15057
>>>> Logged by: Praveen Kumar
>>>> Email address: praveenkumar52028(at)gmail(dot)com
>>>> PostgreSQL version: 9.6.0
>>>> Operating system: Redhat Linux 6
>>>> Description:
>>>>
>>>> Hi Guys,
>>>>
>>>> We have recently migrated our oracle database to PostgreSQL database,and
>>>> migration went successfully.
>>>> We have used ORAFCE to provide backward compatibility.
>>>> But ,unfortunately we stuck with one issue in calling DECODE /TRUNC/or
>>>> any
>>>> other user defined PostgreSQL functions
>>>>
>>>> That is type UNKNOWN
>>>>
>>>> If I try to call a function as below
>>>>
>>>> select myFunction(1,'This is unknown type',90.01,'Again a unknown type
>>>> column');
>>>>
>>>> It is raising an error like function myFunction(integer, unknown, double
>>>> precision, unknown) does not exist.
>>>>
>>>> But in reality, I have this function like myFunction(integer,text,double
>>>> precision,text)
>>>>
>>>> I have gone through all PostgreSQL documents and mail threads but
>>>> couldn't
>>>> find any accurate solution.
>>>> Kindly provide an immediate workaround or a permanent solution.
>>>>
>>>> FYI - I have tried latest PostgreSQL version(PostgreSQL 10) Still no
>>>> use of
>>>> it
>>>>
>>>> select 'hello' return text type
>>>>
>>>> select pg_typeof('hello') returns unknown type
>>>>
>>>> Please help out to fix this.
>>>>
>>>
>>> By default any string literal is of unknown type because it can be
>>> 'AHOJ', '222.22', '2000-12-12'. In next step, PostgreSQL try to detect from
>>> context real type and does retyping.
>>>
>>> for example || operator is defined for text string, so when I write
>>> 'Hello' || 'world' is clean, so both string literal are of text type.
>>> Sometime there is not possible to detect real type - usually when context
>>> is not unambiguous. Then type of string literal stay "unknown".
>>>
>>> you can use explicit typing This is unknown type'::text, or you should
>>> to check some typo error in your code.
>>>
>>> check:
>>>
>>> myFunction(1,'This is unknown type'::text,90.01,'Again a unknown type
>>> column'::text);
>>>
>>> Regards
>>>
>>> Pavel
>>>
>>>
>>>
>>>>
>>>>
>>>> Thanks,
>>>> Praveen
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>
>