Stored procedure SQL Server migration to PostgreSQL
<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>