I fear that I'm about to embarrass myself again. So I'll just ask for 
forgiveness in advance.

Here's a simple test to get started. (All tests are done in a session where I 
set the timezone to 'UTC'.)

drop function if exists f(text)        cascade;
drop function if exists f(timestamp)   cascade;
drop function if exists f(timestamptz) cascade;

create function f(t in text)
  returns text
  language plpgsql
as $body$
begin
  return 'plain "text" overload: '||t;
end;
$body$;

select f('2021-03-15'::date);

This causes the 42883 error, "function f(date) does not exist". I might've 
expected the system to have done an implicit conversion to "text" because this 
conversion is supported, thus:

select f(('2021-03-15'::date)::text);

This succeeds with this result:

plain "text" overload: 2021-03-15

There's clearly a rule at work here. For some reason, the implicit conversion 
from "date" to "text" is not considered to be acceptable.

Now add a plain "timestamp" overload and repeat the test:

create function f(t in timestamp)
  returns text
  language plpgsql
as $body$
begin
  return 'plain "timestamp" overload: '||t::text;
end;
$body$;

select f('2021-03-15'::date);

This succeeds with this result:

plain "timestamp" overload: 2021-03-15 00:00:00

So there's a different rule at work here.  For some reason, the implicit 
conversion from "date" to plain "timestamp" _is_ considered to be acceptable.

Now add a "timesatmptz" overload and repeat the test:

create function f(t in timestamptz)
  returns text
  language plpgsql
as $body$
begin
  return '"timestamptz" overload: '||t::text;
end;
$body$;

select f('2021-03-15'::date);

This succeeds with this result:

"timestamptz" overload: 2021-03-15 00:00:00+00

For some reason, the implicit conversion from "date" to "timestamptz" _is_ 
considered to be preferable to the implicit conversion from "date" to plain 
"timestamp".

I started with "38.6. Function Overloading", followed the link to "Chapter 10. 
Type Conversion" and started with "10.3. Functions". I read "If no exact match 
is found, see if the function call appears to be a special type conversion 
request…" as far as "Note that the “best match” rules are identical for 
operator and function type resolution." So I went to "10.2. Operators" and 
searched in the page for "timestamp". No hits.

Where, in the PG doc, can I find a statement of the rules that allow me to 
predict the outcome of my tests?

Reply via email to