Thread: Casting varchar to timestamp fails in plpgsql

While the following cast works in psql, an equivalent cast fails in plpgsql:

   select cast ('Dec 14 1901 08:45:52' as timestamp);

The following equivalent plgsql function

   create function rdbms_date(varchar) returns timestamp as '
   declare
     p_raw_date alias for $1;
   begin
     return cast (p_raw_date as timestamp);
   end;' language 'plpgsql';

fails with error message:

   ERROR: Cannot cast type 'varchar' to 'timestamp'

when called like so:

   select rdbms_date('Dec 14 1901 08:45:52');

Can someone explain how to cast a varchar to a timestamp in plpgsql?

Thanks,
Bart

Bart Teeuwisse <bart-postgres@7-sisters.com> writes:
> While the following cast works in psql, an equivalent cast fails in plpgsql:

These are not equivalent.

>    select cast ('Dec 14 1901 08:45:52' as timestamp);

What you have here is not a run-time cast, but simply assignment of a
datatype to a literal of previously unspecified type.  The CAST notation
can mean either that or a run-time cast depending on what you apply it to.

> The following equivalent plgsql function
>    create function rdbms_date(varchar) returns timestamp as '
>    declare
>      p_raw_date alias for $1;
>    begin
>      return cast (p_raw_date as timestamp);
>    end;' language 'plpgsql';
> fails with error message:
>    ERROR: Cannot cast type 'varchar' to 'timestamp'

What would actually be equivalent is

test71=# select cast ('Dec 14 1901 08:45:52'::varchar as timestamp);
ERROR:  Cannot cast type 'varchar' to 'timestamp'

However, there is a text-to-timestamp converter, so this works:

test71=# select cast ('Dec 14 1901 08:45:52'::text as timestamp);
        ?column?
------------------------
 1901-12-14 08:45:52-05
(1 row)

So you could cast the varchar argument to text first (or more likely,
declare it as text to begin with).  Or you could simply omit the CAST
and allow default type conversion to occur.  plpgsql would be perfectly
happy to cast varchar to text and thence to timestamp for you --- but
when you put in an explicit CAST, the system assumes that you are trying
to specify an exact type conversion path, and it won't help you out by
silently adding additional conversions (varchar->text in this case).

            regards, tom lane