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