title: PostgreSQL Custom operator compare varchar and integer
body: <p>-- PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-17), 64-bit.</p>
  
  <p>-- Installed from offical repository.</p>
  
  <p>-- No any changes in postgresql.conf .</p>
  
  <p>-- CentOS release 6.8.</p>
  
  <p>-- User: postgres.</p>
  
  <p>-- Used pgAdmin3 LTS by BigSQL.</p>
  
  <p>-- No any unusual log in server.</p>
  
  <p>I have many queries. </p>
  
  <p>in that case I need compare character varying data type (may be a table field) with integer value. </p>
  
  <p>--<strong>Result is True or False</strong> </p>
  
  <pre><code>select '10' = 10; 
  select '10' = '10'; 
  select '10'::character varying = '10'::character varying; 
  select '10'::character varying = 'foo bar'; 
  select '10'::character varying = 'foo bar'::character varying; 
  select 'foo bar' = 'foo bar'; 
  select '10'::character varying = '10';
  </code></pre>
  
  <p>--<strong>Result is "operator does not exist: character varying = integer"</strong> </p>
  
  <pre><code>select '10'::character varying = 10; 
  </code></pre>
  
  <p>so i <strong>create a custom operator</strong> for <strong>compare character varying and integer</strong>. </p>
  
  <p>step 1: create simple function </p>
  
  <pre><code>CREATE OR REPLACE FUNCTION public.is_equal_char_int(character varying, integer) RETURNS boolean AS 
  $BODY$ 
  BEGIN 
      IF $1 = $2::character varying THEN
          RETURN TRUE;
      ELSE 
          RETURN FALSE; 
      END IF;
  End;
  $BODY$ 
  LANGUAGE plpgsql VOLATILE COST 100;
  </code></pre>
  
  <p>step 2: create new operator</p>
  
  <pre><code>CREATE OPERATOR public.=( 
  PROCEDURE = is_equal_char_int,
  LEFTARG = character varying,
  RIGHTARG = integer);
  </code></pre>
  
  <p>so i resoleved my problem and</p>
  
  <pre><code>select '10'::character varying = 10;
  </code></pre>
  
  <p><strong>return true value.</strong></p>
  
  <p>and new problem is:
  when i <strong>compare character varying value with unkown data type</strong> value, postgresql <strong>use my custom operator</strong>.</p>
  
  <pre><code>select '10'::character varying = 'foo bar';
  </code></pre>
  
  <p>result is :</p>
  
  <p><strong>invalid input syntax for integer: "foo bar"</strong></p>
  
  <pre><code>select pg_typeof('foo bar');
  </code></pre>
  
  <p><strong>return unkown data type.</strong></p>
  
  <p>and next step I create new operator for compare character varying and unkown data type.</p>
  
  <p>Step 1:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION public.is_equal_char_unknown(character varying, unknown)
  RETURNS boolean AS
  $BODY$
  BEGIN
  IF $1 = $2::character varying THEN
      RETURN TRUE;
  ELSE
      RETURN FALSE;
  END IF;
  End;
  $BODY$
  LANGUAGE plpgsql VOLATILE COST 100;
  </code></pre>
  
  <p>step 2:</p>
  
  <pre><code>CREATE OPERATOR public.=( 
  PROCEDURE = is_equal_char_unknown,
  LEFTARG = character varying,
  RIGHTARG = unknown);
  </code></pre>
  
  <p>when I run</p>
  
  <pre><code>select '10'::character varying = 'foo bar';
  </code></pre>
  
  <p>I give</p>
  
  <p><strong>ERROR:  operator is not unique: character varying = unknown.</strong> </p>
  
  <p>So I'm in a hole.</p>