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

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