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

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.

I could replicate this even now.
Available plugin

[image: Inline image 2]
Version
[image: Inline image 1]

PostgreSQL BUG :

[image: Inline image 3]

Let me know if anything else required to show this as PostgreSQL bug.

Thanks,
Praveen

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