title: postgresql: creating a text to integer multiplication operator
body: <p>I am looking to debug a Microsoft SQL query inside of Posgresql (long story short I prefer the error messages it returns) when I came across some SQL that was multiplying a substring select with an integer, now I could obviously cast each part using <code>::integer</code> however there are many substrings that need casting and many queries I wanted to run through so I decided that I might make my life easier by using an operator to address this with the use of the following function.</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION public.multiplytext2int(text,integer)
  RETURNS integer AS
  $$
  SELECT CASE
          WHEN $1 ~ '^[0-9]+$'
          THEN $1::integer * $2
          ELSE 0::integer
      END
  $$
  LANGUAGE sql IMMUTABLE;
  </code></pre>
  
  <p>obviously the logic is not completely airtight but will suffice for the queries I will be running, so then I try to create the operator</p>
  
  <pre><code>CREATE OPERATOR * (
       LEFTARG = text
      ,RIGHTARG = integer
      ,FUNCTION = multiplytext2int
  )
  </code></pre>
  
  <p>when I run this I get the following error</p>
  
  <blockquote>
    <p>WARNING:  operator attribute "function" not recognized ERROR: 
    operator procedure must be specified</p>
    
    <p>********** Error **********</p>
    
    <p>ERROR: operator procedure must be specified SQL state: 42P13</p>
  </blockquote>
  
  <p>what am I doing wrong here?</p>
  
  <p>ultimately I am looking to be able to be able to type <code>SELECT '1'::text * 2::integer</code> without an operator does not exist error.</p>