title: Stored procedure SQL Server migration to PostgreSQL
body: <p>I'm migrating stored procedures from SQL Server to PostgreSQL. I have converted the stored procedure to a Postgres function. </p>
  
  <p>SQL Server stored procedure:</p>
  
  <pre><code>CREATE PROCEDURE AddFullSig @CandID int, @FullSig binary(16)
  AS
      if not exists (select pub_id 
                     from local_lib 
                     where cand_id = @CandID and Full_sig = @FullSig)
          insert into local_lib 
          values(@CandID, 1, @FullSig)
      else
          update local_lib 
          set dup_count = dup_count + 1 
          where cand_id = @CandID 
            and Full_sig = @FullSig
  
      select pub_id 
      from local_lib 
      where cand_id = @CandID and Full_sig = @FullSig
  
      RETURN
  </code></pre>
  
  <p>Postgres function:</p>
  
  <pre><code>create type addfullsig_return_type as(
    pub_id int 
  );
  
  create or replace function addfullsig( p_candid int, p_fullsig bytea) 
  returns addfullsig_return_type as $$
  begin
  
  if not exists(select pub_id from local_lib where cand_id = p_candid and full_sig = p_fullsig) then
      insert into local_lib values(default, p_candid, 1, p_fullsig);
  else
      update local_lib set dup_count = dup_count + 1 where cand_id = p_candid and full_sig = p_fullsig;
  end if;
  
  select pub_id from local_lib where 
  cand_id = p_candid and full_sig = p_fullsig;
  end;
  $$ language plpgsql;
  </code></pre>
  
  <p>When I try to test this in pgadmin using:</p>
  
  <pre><code>select * from addfullsig(3,1010111011111011);
  </code></pre>
  
  <p>I get the error:</p>
  
  <blockquote>
    <p>ERROR:  function addfullsig(integer, bigint) does not exist</p>
  </blockquote>
  
  <p>I'm not sure if my conversion to postgresql is correct, especially in using bytea for binary(16) instead of bit(16). Any help or insight would be appreciated.  </p>