Re: Functions with COPY

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > I guess my example was lacking, I'm sure there are cases where the
> > text->date casting will end up being wrong or some date style won't be
> > accepted. If the above was 'January 10th, 2003, October 1st, 2003,
> > January 1st, 2003', for example. Thinking back I think that might have
> > been the situation I was thinking about (conflicting mdy and dmy) and
> > would have made more sense as an example.
>
> Then what are you after, some magically prescient input mode that will
> guess the correct interpretation?

No, I'm interested, as I discussed in my message[1], in the ability to
use functions in a copy statement to allow me to specify the conversion
from text to the appropriate data type. Right now Postgres is using
casting which can end up being wrong. That's not a fault or something
that can be fixed, the casting logic itself is fine but it's not always
appropriate to apply the same casting to all fields of a given type.

It would be nice to be able to choose what function is used and to pass
arguments to it. This is a feature request and I'm not suggesting a
change in host the text->date casting is done. From a programmatical
standpoint I see things like this:

Right now:
text -> date : cast(text as date)
text -> numeric : cast(text as numeric)

I want to be able to pop that out and put my own function in place of
it, like so:
text -> date : to_date(text,'YYYY-Mon-DD')
text -> numeric : to_numeric(text,'99V99')

My other message had an example syntax to do this. I don't know if
that'd be the appropriate syntax or not but I thought it illustrated
what I was interested in.

Thanks,

Stephen

[1] http://archives.postgresql.org/pgsql-hackers/2003-11/msg01456.php