[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] User-defined operator function: what parameter type to use for uncast character string
From:       Kevin Grittner <kgrittn () ymail ! com>
Date:       2014-07-31 13:46:49
Message-ID: 1406814409.89137.YahooMailNeo () web122301 ! mail ! ne1 ! yahoo ! com
[Download RAW message or body]

Adam Mackler <pgsql-general@mackler.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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[prev in list] [next in list] [prev in thread] [next in thread]