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! {-;