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

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