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