Re: Functions with COPY

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Functions with COPY
Date: 2003-11-28 17:30:08
Message-ID: 20031128173008.GI24094@ns.snowman.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

* 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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2003-11-28 17:31:08 Re: -fpic vs. -fPIC
Previous Message Tom Lane 2003-11-28 17:15:35 Re: Functions with COPY