PostgreSQL Discussion - Auto convert for type?

Author

Auto convert for type?

karly@kipshouse.org

2006-03-28, 8:26 pm

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/...e/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! {-;

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Tom Lane

2006-03-28, 8:26 pm

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

karly@kipshouse.org

2006-03-28, 8:26 pm

On Tue, Mar 28, 2006 at 03:34:18PM -0500, Tom Lane wrote:
> karly@kipshouse.org writes:
>
> 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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Tom Lane

2006-03-28, 8:26 pm

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Sponsored Links