Re: BUG #15057: Issue with UNKNOW type when calling a PostgreSQL function from java code - Mailing list pgsql-bugs

My functions are not ambiguous they are defined with proper types.

I have another question,

May I know how is PostgreSQL able to define its TYPE in the below scenario.

May I also request for some feed on the term COLLISION .

Thanks,

Praveen

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

2018-02-10 10:12 GMT+01:00 Praveen Kumar <praveenkumar52028@gmail.com>:

I understand that Pavel,

But, in our context, we always have text/varchar2 inside single quotes,

Postgresql cannot to know it.

 

Do we have any workaround ,to make PostgreSQL parse think single quoted string as text ?

explicit typing is correct solution for Postgres.

You can write own functions, where context will be clear and not ambiguous - and that is all. Check your functions, maybe you have forgotten collision.

Thanks,

Praveen.K

 

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

2018-02-10 9:50 GMT+01:00 Praveen Kumar <praveenkumar52028@gmail.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. ?

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