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
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 |