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

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