Auto convert for type?

From: karly(at)kipshouse(dot)org
To: pgsql-general(at)postgresql(dot)org
Subject: Auto convert for type?
Date: 2006-03-28 20:13:06
Message-ID: 20060328121306.A21900@kipshouse.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2006-03-28 20:34:18 Re: Auto convert for type?
Previous Message Ron Mayer 2006-03-28 19:49:31 Re: FAQ 1.1