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

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.

Another use case,

Here I just queried an integer without specifying ::integet ,but how could pg_typeof know that is an integer. ?

Thanks,

Praveen

On Sat, Feb 10, 2018 at 2:09 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2018-02-10 9:22 GMT+01:00 Praveen Kumar <praveenkumar52028@gmail.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.stehule@gmail.com> wrote:

Hi

this is not PostgreSQL bug

2018-02-10 8:57 GMT+01:00 PG Bug reporting form <noreply@postgresql.org>:

The following bug has been logged on the website:

Bug reference:      15057
Logged by:          Praveen Kumar
Email address:      praveenkumar52028@gmail.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