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

Adam Mackler <pgsql-general(at)mackler(dot)org> wrote:

>     CREATE domain my_domain as char(3) check(VALUE similar to '[A-C]{3}');
>     CREATE TABLE my_table (val my_domain);
>     INSERT INTO my_table VALUES ('ABC');

>     sandbox=> SELECT * FROM my_table WHERE val='abc';
>     val
>     -----
>     (0 rows)
>
> Question: What can I do so my custom equality operator is used without
> the cast?

If you are trying to get this particular case to work, you are
going about it the hard way, since case-insensitive comparisons are
supported by the citext extension.  This particular case could be
solved by:

CREATE EXTENSION citext;

CREATE domain my_domain as citext check(VALUE ~ '^[A-C]{3}$');
CREATE TABLE my_table (val my_domain);
INSERT INTO my_table VALUES ('ABC');

SELECT * FROM my_table WHERE val = 'abc';

If this is just a simplified example, you might want to look at the
CREATE CAST statement.

http://www.postgresql.org/docs/current/interactive/sql-createcast.html

Or look through the source code for the citext extension to see
what other sorts of issues you might need to cover.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company