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

2018-02-10 10:31 GMT+01:00 Praveen Kumar <praveenkumar52028(at)gmail(dot)com>:

> 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.
>

It is different context

> [image: Inline image 1]
>
> May I also request for some feed on the term COLLISION .
>

maybe you have two or more functions with same name

>
>
> 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
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>