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

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