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

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)

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