Stored procedure SQL Server migration to PostgreSQL

Question

I'm migrating stored procedures from SQL Server to PostgreSQL. I have converted the stored procedure to a Postgres function.

SQL Server stored procedure:

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

Postgres function:

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;

When I try to test this in pgadmin using:

select * from addfullsig(3,1010111011111011);

I get the error:

ERROR: function addfullsig(integer, bigint) does not exist

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.

Your second parameter is a bytea not a long value. See the manual for details on how to pass a "blob" literal: postgresql.org/docs/current/static/datatype-binary.html — user330315
– user330315, Commented May 12, 2016 at 21:37
And it would be more efficient (and robust) to use insert on conflict instead of the if statement. — user330315
– user330315, Commented May 13, 2016 at 6:02

Stijn Sanders · Accepted Answer · 2016-05-12 21:48:28Z

2

The sequence of 1's and 0's is parsed as an integer literal and guessed it's of type bigint.

Use the bit string syntax:

select * from addfullsig(3,B'1010111011111011');

See more here

answered May 12, 2016 at 21:48

Stijn Sanders

37k11 gold badges48 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Stored procedure SQL Server migration to PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related