Re: User-defined operator function: what parameter type to use for uncast character string?

On Thursday, July 31, 2014, Adam Mackler-5 [via PostgreSQL] <
ml-node+s1045698n5813494h38(at)n5(dot)nabble(dot)com
<javascript:_e(%7B%7D,'cvml','ml-node%2Bs1045698n5813494h38(at)n5(dot)nabble(dot)com');>>
wrote:

> On Thu, Jul 31, 2014 at 10:03:00AM -0400, Tom Lane wrote:
> > 2. text is the preferred type among the string class, so any case where
> > you have text on one side and some other string type on the other is
> > going to get resolved as text vs text.
> > Because of #1, domain-specific functions and operators tend to be pretty
> > useless; you find yourself always having to cast the other side to get
> >
> > If you're intent on having this behavior, the way to go at it is to make
> > your own actual datatype (not a domain) and create all your own
> comparison
> > operators for it. You can add an implicit cast to text for cases where
>
> Thanks to everyone who responded to my question. Yes, the
> case-insensitivity was an example contrivance. My actual operator
> function is using regular expressions to do text replacements, so
> citext is not going to solve all my problems.

Looks like 'CREATE TYPE' is the only way to get what I'm after here, and
> since there are
> regular expressions involved, a C-language solution is not seeming
> like it's going to be very convenient. On top of that, the content of
> my regular-expression replacement strings are constructed from data
> in my database, so really my SQL-language operator function seemed
> ideal except--of course--for the limitations you all have explained to
> me.

I'm obviously under informed but you may wish to step back and consider
whether you are being too clever/novel...

One area you need to evaluate is how what you do interplays with indexing -
if necessary - and performance generally.

> One final question: the 'CREATE CAST' command got my interest. I'm
> assuming that when the docs say it 'performs a conversion between two
> data types,' that the meaning of "data type" includes only those
> created using 'CREATE TYPE' and excludes domains. If I am mistaken on
> that point I would be grateful to learn of that mistake.
>
>
Seems easy enough to test...though since 'value'::domain is valid syntax I
would have to assume your conclusion is wrong.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/User-defined-operator-function-what-parameter-type-to-use-for-uncast-character-string-tp5813386p5813504.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.