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

My functions are not ambiguous they are defined with proper types.

I have another question,
May I know how is PostgreSQL able to define its TYPE in the below scenario.
[image: Inline image 1]

May I also request for some feed on the term COLLISION .

Thanks,
Praveen

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

>
>
> 2018-02-10 10:12 GMT+01:00 Praveen Kumar <praveenkumar52028(at)gmail(dot)com>:
>
>> I understand that Pavel,
>> But, in our context, we always have text/varchar2 inside single quotes,
>>
>
> Postgresql cannot to know it.
>
>
>>
>> Do we have any workaround ,to make PostgreSQL parse think single quoted
>> string as text ?
>>
>
> explicit typing is correct solution for Postgres.
>
> You can write own functions, where context will be clear and not ambiguous
> - and that is all. Check your functions, maybe you have forgotten collision.
>
>
>
>
>
>>
>> Thanks,
>> Praveen.K
>>
>>
>> On Sat, Feb 10, 2018 at 2:27 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
>> wrote:
>>
>>>
>>>
>>> 2018-02-10 9:50 GMT+01:00 Praveen Kumar <praveenkumar52028(at)gmail(dot)com>:
>>>
>>>> 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.
>>>>
>>>
>>> PostgreSQL is not Oracle, or MySQL. It has different type system with
>>> different advantages and disadvantages.
>>>
>>> PostgreSQL parser generate some type info, when it is possible
>>>
>>> 11111 --> integere
>>> 1111.22 --> numeric
>>> 'xxxxx' --> unknown, because
>>>
>>> * 'hello' ... text
>>> * '20180210' ... date
>>> * '{xxx,xxxxs,dddd,kkkk}' ... array
>>> * '(10,22,hhh,kkk)' ... composite type
>>> * '[10, 20, 30]' ... json maybe jsonb
>>> * 'WKB (....)' ... postgresql custom type
>>>
>>> There is not possible to detect just from value used type. Against
>>> Oracle or MySQL, PostgreSQL is very expandable -- there is possibility to
>>> have custom types, custom functions, ... that means so PostgreSQL
>>> extendible type system is very different from other databases.
>>>
>>>
>>>
>>>>
>>>> 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
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>