PostgreSQL - Auto Cast for types?

Question

I'm working on porting database from Firebird to PostgreSQL and have many errors related to type cast. For example let's take one simple function:

CREATE OR REPLACE FUNCTION f_Concat3 (
  s1 varchar, s2 varchar, s3 varchar
)
RETURNS varchar AS
$body$
BEGIN
   return s1||s2||s3;
END;
$body$ LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER LEAKPROOF COST 100;

As Firebird is quite flexible to types this functions was called differently: some of the arguments might be another type: integer/double precision/timestamp. And of course in Postgres function call f_Concat3 ('1', 2, 345.345) causes an error like:

function f_Concat3(unknown, integer, numeric) not found.

The documentation is recomended to use an explicit cast like:

f_Concat3 ('1'::varchar, 2::varchar, 345.345::varchar) 

Also I can create a function clones for all possible combinations of types what might occur and it will work. An example to resolve error:

CREATE OR REPLACE FUNCTION f_Concat3 (
   s1 varchar, s2 integer, s3 numeric
)
RETURNS varchar AS
$body$
  BEGIN
    return s1::varchar||s2::varchar||s3::varchar;
  END;

However this is very bad and ugly and it wont work with big functions.

Important: We have one general code base for all DB and use our own language to create application objects (forms, reports, etc) which contains select queries. It is not possible to use explicit cast on function calls cause we will lose compatibility with other DB.

I am confused that the integer argument can not be casted to the numeric or double precision, or date / number to a string. I even face problems with integer to smallint, and vice versa. Most database act not like this.

Is there any best practice for such situation?
Is there any alternatives for explicit cast?

Answer 1

SQL is a typed language, and PostgreSQL takes that more seriously than other relational databases. Unfortunately that means extra effort when porting an application with sloppy coding.

It is tempting to add implicit casts, but the documentation warns you from creating casts between built-in data types:

Additional casts can be added by the user with the CREATE CAST command. (This is usually done in conjunction with defining new data types. The set of casts between built-in types has been carefully crafted and is best not altered.)

This is not an idle warning, because function resolution and other things may suddenly fail or misbehave if you create new casts between existing types.

I think that if you really don't want to clean up the code (which would make it more portable for the future), you have no choice but to add more versions of your functions.

Fortunately PostgreSQL has function overloading which makes that possible.

You can make the job easier by using one argument with a polymorphic type in your function definition, like this:

CREATE OR REPLACE FUNCTION f_concat3 (
   s1 text, s2 integer, s3 anyelement
) RETURNS text
   LANGUAGE sql IMMUTABLE LEAKPROOF AS
'SELECT f_concat3(s1, s2::text, s3::text)';

You cannot use more than one anyelement argument though, because that will only work if all such parameters are of the same type.

If you use function overloading, be careful that you don't create ambiguities that would make function resolution fail.