Thread: Auto convert for type?

Many of my tables have a timestamp column, which I store as
TIMESTAMP WITH TIME ZONE.  Often I only want to return the date from
this field, and it may need a specific format.

All of the queries are passed through PLpqsql functions that return
either a record or a set of records.  I find if I declare the date
field in the return record as type DATE, then the conversion
happens automatically, which is nice, but the format is whatever
datestyle is set to, which isn't what I want.

I can create the date field in the record as TEXT, then use
to_char() to convert it, but I have to do that in every function.

Ideally I could do something like

CREATE TYPE return AS (..., mydate to_char(DATE, 'mm/dd/yy'));


but that syntax doesn't work.  I thought I might be able to create
my own type, and have an implicit conversion of DATE, but I read

http://www.postgresql.org/docs/8.0/interactive/typeconv.html

and found this line


   User-defined types are not related. Currently, PostgreSQL does not
   have information available to it on relationships between types,
   other than hardcoded heuristics for built-in types and implicit
   relationships based on available functions and casts.

Is there another way to solve this?

Thanks

-karl

PS Now when I type pgsql, I think "PigSqueal"  Thanks a lot!  {-;

karly@kipshouse.org writes:
> but that syntax doesn't work.  I thought I might be able to create
> my own type, and have an implicit conversion of DATE,

You can *make* an implicit cast from (or to) DATE, but there won't be
one made for you.

            regards, tom lane

On Tue, Mar 28, 2006 at 03:34:18PM -0500, Tom Lane wrote:
> karly@kipshouse.org writes:
> > but that syntax doesn't work.  I thought I might be able to create
> > my own type, and have an implicit conversion of DATE,
>
> You can *make* an implicit cast from (or to) DATE, but there won't be
> one made for you.

Yes, that's what I'm looking for.  How can I create an IMPLICIT
cast from date to my custom type?  IOW, if I assing a date to a
varible of my type, it will be converted to the format I specify.
I don't seem to be able to find that in the online doc, though I'm sure
it's there somewhere.


Thanks

-karl

karly@kipshouse.org writes:
> Yes, that's what I'm looking for.  How can I create an IMPLICIT
> cast from date to my custom type?  IOW, if I assing a date to a
> varible of my type, it will be converted to the format I specify.

Make a function that does the conversion the way you want, and then
create a cast that uses the function (see CREATE CAST).

            regards, tom lane