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

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

> Yes, Pavel, I do have multiple functions with same name and different
> number/types of parameters.
>
> Is there any problem with this kind of function creation (I mean method
> overloading)
>

depends on design - sometimes, it can require explicit typing.

regards

Pavel

>
>
> Thanks.
> Praveen
>
> On Sat, Feb 10, 2018 at 3:31 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
>>
>>
>> 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
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>