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

From: Adam Mackler <pgsql-general(at)mackler(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: User-defined operator function: what parameter type to use for uncast character string?
Date: 2014-07-31 05:33:25
Message-ID: 20140731053324.GA94831@scruffle.mackler.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

(Cross-posted to StackOverflow: http://stackoverflow.com/questions/25041100/postgresql-user-defined-operator-function-what-parameter-type-to-use-for-uncast )

I'm defining my own domain and a equality operator. I cannot cause
PostgreSQL to use my operator function in a query without explicitly
casting a character-string argument to the type I used in defining the
operator. My question is how to cause my custom operator to be used
without the cast.

As an example: first I define a domain and a table column of that
type. For this example, the type requires its values to be three
uppercase letters A, B or C, and the equality operator will cause
queries to match regardless of case.

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');

The type of the column is my_domain:

sandbox=> \d my_table
Table "public.my_table"
Column | Type | Modifiers
--------+-----------+-----------
val | my_domain |

Before defining the custom equality operator, case-sensitive queries
work as I expect. The row in the table is capital letters, so the
query must contain capital letters to match the row

sandbox=> SELECT * FROM my_table WHERE val='abc';
val
-----
(0 rows)

sandbox=> SELECT * FROM my_table WHERE val='ABC';
val
-----
ABC
(1 row)

Next I create an equality operator to do case-insensitive matching:

CREATE FUNCTION my_equals(this my_domain, that text) RETURNS boolean AS
'SELECT CAST (this AS text) = upper(that)' LANGUAGE SQL;
CREATE OPERATOR = (procedure=my_equals, leftarg=my_domain, rightarg = text);

The new operator is invoked causing a query containing lowercase
letters to match the uppercase column value, but only if I cast the
type of the WHERE clause:

sandbox=> SELECT * FROM my_table WHERE val=CAST ('abc' AS text);
val
-----
ABC
(1 row)

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? In other words, how to cause the last query above return
the table row (without changing the query)? I have tried defining
my_equals() so its second parameter type is either varchar and
char(3), but those still require a cast in the WHERE-clause of the
query. I've also tried anyelement, but that does not work even with a
cast.

Thank you,
--
Adam Mackler

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G Johnston 2014-07-31 05:59:28 Re: User-defined operator function: what parameter type to use for uncast character string?
Previous Message Tonny 2014-07-31 05:25:46 BDR Postgres