Subprogram overloading

Subprograms with different subprogram_call_signatures can share the same subprogram_name. If two or more subprograms share the same subprogram_name, then the subprogram_name is said to be overloaded. Notice the relationship between the subprogram_signature rule:

• Grammar
• Diagram

subprogram_signature ::= arg_decl [ , ... ]

arg_decl ::= [ arg_name ] [ arg_mode ] arg_type

arg_name ::= name

arg_mode ::= IN | OUT | INOUT | VARIADIC

arg_type ::= type_name

and the subprogram_call_signature rule:

• Grammar
• Diagram

subprogram_call_signature ::= arg_type [ , ... ]

The subprogram_signature is a list of arg_decls; and an arg_decl has two optional components (arg_type and arg_mode) and one mandatory component (arg_type). But the only significant part of the subprogram_signature for distinguishing between overloads is the mandatory arg_type component.

Negative examples

Attempt to create two functions that differ only by the names of their single argument.

drop schema if exists s1 cascade;
create schema s1;

create function s1.f(i in int)
  returns int
  language sql
as $body$
  select i*2;
$body$;

create function s1.f(j in int)
  returns int
  language sql
as $body$
  select j*2;
$body$;

The second create function attempt causes the 42723 error, function "f" already exists with same argument types. The names of the arguments, and their modes, are insignificant for distinguishing between overload candidates.

With the function s1.f(i in int) still in place, now try this:

create function s1.f(i in int)
  returns text
  language sql
as $body$
  select (i*2)::text;
$body$;

This, too, causes the 42723 error. These two versions of s1.f() have identical subprogram_call_signatures and differ only in the data types that they return. This difference, too, is insignificant for distinguishing between overload candidates.

With the function s1.f(i in int) still in place, now try this:

create procedure s1.f(i in int)
  language sql
as $body$
begin
  raise info 'procedure f(i in int)';
end;
$body$;

Once again, you get the 42723 error. The function and the procedure have the same subprogram_call_signature—but the kind of the subprogram, function or procedure is also insignificant for distinguishing between overload candidates.

The only significant information for distinguishing between overload candidates is the subprogram_call_signature.

Simple positive, but contrived, example

Try this:

drop schema if exists s1 cascade;
create schema s1;

create function s1.f(i in text)
  returns text
  language plpgsql
as $body$
begin
  return 'text overload';
end
$body$;

drop function if exists f(varchar) cascade;
create function s1.f(i in varchar)
  returns text
  language plpgsql
as $body$
begin
  return 'varchar overload';
end
$body$;

select
  (select s1.f('dog'::text))     as "typecast to 'text'",
  (select s1.f('dog'::varchar))  as "typecast to 'varchar'";

This is the result:

 typecast to 'text' | typecast to 'varchar'
--------------------+-----------------------
 text overload      | varchar overload

Now leave out the explicit typecast:

select s1.f('dog');

This is the result:

       f
---------------
 text overload

Now drop the text overload and repeat the identical query:

drop function s1.f(text);
select s1.f('dog');

This is the new result:

        f
------------------
 varchar overload

These two query results emphasize how treacherous the terrain is. The literal 'dog' is legal both as a text value and as a varchar value; and nothing about it indicates which is preferred. A committee decided, doubtless after careful thought, that:

• The interpretation of the unadorned literal _'dog' as a text value is preferred over the interpretation as a varchar value—when both candidates exist.
• The interpretation of the unadorned literal _'dog' as a varchar value allowed when no better candidate exists.

Notice that, now, even this attempt, where and explicit typecast to text is used, still resolves to the varchar candidate without error.

select s1.f('dog'::text);

Repeat this test but add a char overload into the mix. Experiment with various orders of dropping until you get down to just a single remaining overload. (This implies six different experiments.) The results might confuse you—and you'd probably find it hard to summarize them succinctly.

Most ordinary programmers find it impossible to memorise the actual rules that govern scenarios like these and to rely on them safely. The next section pursues this point with some more examples.

Choosing between overload candidates in the absence of a perfect match

The following examples hammer home the point that the rules for choosing which overload to use when there is more than one plausible candidate, and which implicit typecasts are supported, are tortuous.

First create the basic setup with a text overload, an int overload, and a boolean overload of the name s1.f:

drop schema if exists s1 cascade;
create schema s1;

create function s1.f(i in text)
  returns text
  language plpgsql
as $body$
begin
  return 'text overload';
end
$body$;

drop function if exists s1.f(int) cascade;
create function s1.f(i in int)
  returns text
  language plpgsql
as $body$
begin
  return 'int overload';
end
$body$;

drop function if exists s1.f(boolean) cascade;
create function s1.f(i in boolean)
  returns text
  language plpgsql
as $body$
begin
  return 'boolean overload';
end
$body$;

select
  (select s1.f('dog'))  as "s1.f('dog')",
  (select s1.f(17))     as "s1.f(17)",
  (select s1.f(true))   as "s1.f(true))";

This is the result:

  s1.f('dog')  |   s1.f(17)   |   s1.f(true))
---------------+--------------+------------------
 text overload | int overload | boolean overload

Now do this:

drop function s1.f(text);

select
  (select s1.f('42'))  as "s1.f('42')",
  (select s1.f(17))    as "s1.f(17)";

The drop leaves behind the int and the boolean overloads. But, because the string 'true' can be typecast to boolean, the select causes this error:

42725: function s1.f(unknown) is not unique

Remove the source of confusion and try the query again:

drop function s1.f(boolean);

select
  (select s1.f('42'))  as "s1.f('42')",
  (select s1.f(17))    as "s1.f(17)";

Now the error goes away and you get this result:

  s1.f('42')  |   s1.f(17)
--------------+--------------
 int overload | int overload

Now remove the int overload and put back the boolean overload and try a different query:

drop function s1.f(int);
create function s1.f(i in boolean)
  returns text
  language plpgsql
as $body$
begin
  return 'boolean overload';
end
$body$;

select
  (select s1.f(true))    as "s1.f(true)",
  (select s1.f('true'))  as "s1.f('true')";

Again, there's no error; and you get this result:

    s1.f(true)    |   s1.f('true')
------------------+------------------
 boolean overload | boolean overload

Finally, put back the text overload and repeat the same query:

create function s1.f(i in text)
  returns text
  language plpgsql
as $body$
begin
  return 'text overload';
end
$body$;

select
  (select s1.f(true))    as "s1.f(true)",
  (select s1.f('true'))  as "s1.f('true')";

This time, the fact that 'true' can be implicitly converted to a boolean does not cause the "function s1.f(unknown) is not unique" error. Rather, this is the result:

    s1.f(true)    | s1.f('true')
------------------+---------------
 boolean overload | text overload