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

Thanks,
Praveen

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

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

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

Another use case,

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

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

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. ?
[image: Inline image 1]

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

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. ?
[image: Inline image 1]

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

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. ?
[image: Inline image 1]

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

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. ?
[image: Inline image 1]

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

"PG" == PG Bug reporting form <noreply@postgresql.org> writes:

PG> If I try to call a function as below

PG> select myFunction(1,'This is unknown type',90.01,'Again a unknown
PG> type column');

PG> It is raising an error like function myFunction(integer, unknown,
PG> double precision, unknown) does not exist.

I think you need to be a bit more specific about what precisely you did,
because this is what I tried:

create or replace function
myfunc(a integer, b text, c double precision, d text)
returns void language plpgsql
as $$ begin raise info 'myfunc called'; end; $$;

select myfunc(1,'foo',90.01,'bar');
INFO: myfunc called

In particular: do you have more than one function of the same name? Were
you actually calling the function from JDBC with parameters (and if so
what types) rather than as a plain select? Show us an exact transcript,
not just your editorialized summary.

--
Andrew (irc:RhodiumToad)

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

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.

It is different context

[image: Inline image 1]

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

maybe you have two or more functions with same name

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. ?
[image: Inline image 1]

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

Thanks.
Praveen

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

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

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.

It is different context

[image: Inline image 1]

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

maybe you have two or more functions with same name

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. ?
[image: Inline image 1]

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

By the way, PostgreSQL is allowing me to do method overloading,So I am
able to write multiple functions with the same name and different parameters

Thanks,
Praveen

On Sat, Feb 10, 2018 at 3:37 PM, Andrew Gierth <
andrew@tao11.riddles.org.uk> wrote:

"PG" == PG Bug reporting form <noreply@postgresql.org> writes:

PG> If I try to call a function as below

PG> select myFunction(1,'This is unknown type',90.01,'Again a unknown
PG> type column');

PG> It is raising an error like function myFunction(integer, unknown,
PG> double precision, unknown) does not exist.

I think you need to be a bit more specific about what precisely you did,
because this is what I tried:

create or replace function
myfunc(a integer, b text, c double precision, d text)
returns void language plpgsql
as $$ begin raise info 'myfunc called'; end; $$;

select myfunc(1,'foo',90.01,'bar');
INFO: myfunc called

In particular: do you have more than one function of the same name? Were
you actually calling the function from JDBC with parameters (and if so
what types) rather than as a plain select? Show us an exact transcript,
not just your editorialized summary.

--
Andrew (irc:RhodiumToad)

"Praveen" == Praveen Kumar <praveenkumar52028@gmail.com> writes:

Praveen> [image: Inline image 2]

In future please use text rather than screenshots, so that you can
include the complete list rather than just a small part.

Praveen> While calling one of these DECODE functions from my existing
Praveen> select query,I am seeing an error saying

Praveen> decode(numeric,integer,unknown,unknow) is not available

Because you didn't include the complete list of function signatures in
your message, we're left with the fact that the ones you _did_ show all
start out with anyelement,anyelement. Since polymorphic function
resolution requires all "anyelement" matches to be of the _same_ type,
it's clear that none of these can match a call which has numeric,integer
as the first two parameters.

Even if you changed your second parameter to be numeric rather than
integer, you'd then be faced with the fact that the list of signatures
contains at least these:

decode(anyelement,anyelement,bigint,bigint)
decode(anyelement,anyelement,character,character)
decode(anyelement,anyelement,date,date)

So how would postgres be able to tell, given an argument list with types
(numeric,numeric,unknown,unknown), which of these signatures to use?
Obviously it would be ambiguous.

--
Andrew (irc:RhodiumToad)