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

I understand that Pavel,
But, in our context, we always have text/varchar2 inside single quotes,

Do we have any workaround ,to make PostgreSQL parse think single quoted
string as text ?

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