Banner Top

Due to modern long-term trend of reducing expenses on software licenses, many companies migrate their SQL Server databases to PostgreSQL. One of the most complicated parts of database migration is translating source code of stored procedures and functions into the destination SQL dialect. The process of migrating MS SQL code to PostgreSQL must include the steps below:

  1. Unlike SQL Server, In PostgreSQL ‘DECLARE’ keyword must be used at the begin of variables definition section
  2. Output parameters must be transformed from ‘name type OUT’ into ‘OUTPUT name type’, optional keyword ‘AS’ between name and type must be omitted
  3. Symbol ‘@’ in front of every variable/parameter name must be removed or replaced by ‘v’ to indicate it is a variable/parameter name
  4. RETURN clause within a function prototype must be converted into ‘RETURNS’
  5. PostgreSQL requires language specification at the end of each function using ‘$$ LANGUAGE plpgsql;’ pattern
  6. SQL Server can return dynamic tables, while PostgreSQL cannot. Therefore it requires either table is defined in RETURNS section or returned type is set to SETOF RECORD and :

select * from func_name(parameter) f(col1int, col2 text, …);

  1. System functions of SQL Server must be converted into PostgreSQL equivalents according to the table below
SQL Server PostgreSQL
CHARINDEX ($ToFind, $ToSearch) POSITION($ToFindin $ToSearch)
CONTAINS ($str, $pattern) $str LIKE ‘%$pattern%’
DATEADD($interval,$units,$date) $date + $n_units * $interval
DATEDIFF ($interval, $date1, $date2) DATE_PART ($interval, $date2 – $date1).
DAY ($date) DATE_PART (‘day’, $date)
GETDATE() NOW()
GETUTCDATE() NOW() at time zone ‘utc’
ISNULL COALESCE
LEN($a) LENGTH($a)
MONTH ($date) DATE_PART (‘month’, $date)
REPLICATE($a, $ntime) REPEAT ($a, $ntime)
SPACE($ntime) REPEAT (‘ ‘, $ntime)
YEAR($date) DATE_PART (‘year’, $date)

Handling Errors

SQL Server exposes errors that are below some critical level through system variable @@ERROR.All errors above that level terminates execution immediately and displays error message. PostgreSQL uses SQLSTATE for the same purpose. Also, it provides the statement ‘EXCEPTION WHEN … THEN …’to handle particular classes of errors. Specify exception class ‘OTHERS‘ in order to catchall types of errors.

To initiate exception in PostgreSQL use ‘RAISE {error message} USING ERRCODE = {error code}’. More information about handling errors in PostgreSQL is available here.

The following example illustrates migration of stored function from SQL Server to PostgreSQL using the rules listed above. Assume, we have MS SQL source code defined as:

CREATE PROCEDURE [dbo].[CHECKREFERENCES]

(

@P_ACCOUNTID decimal(12),

@P_CHECKREFERENCES int OUTPUT,

@P_ERROR int OUTPUT

)

AS

SET @P_ERROR = 0

DECLARE @REFCOUNT decimal(12)

SELECT @REFCOUNT=COUNT(*) FROM REFERENCES

WHERE ID = @P_ACCOUNTEID

IF (@REFCOUNT>0)

BEGIN

SET @P_CHECKREFERENCES = 1

END

ELSE

BEGIN

SET @P_CHECKREFERENCES = 0

END

IF (@@ERROR <> 0)

BEGIN

SET @P_ERROR = 1

raiserror(‘Select error from STRUCTURES ‘,16,1);

END

RETURN

It should be migrated to PostgreSQL as follows:

CREATE OR REPLACE FUNCTION ACCOUNTEMPLOYEES_CHECKREFERENCES

(

vP_ACCOUNTEIDdecimal(12),

OUT vP_CHECKREFERENCES int,

OUT vP_ERROR int

)

AS $$

DECLARE v_REFCOUNTdecimal(12);

BEGIN

/* By default we have no error */

vP_ERROR := 0;

SELECT COUNT(*) INTO v_REFCOUNT FROM REFERENCES

WHERE ID = vP_ACCOUNTEID;

IF (v_REFCOUNT>0)

THEN

vP_CHECKREFERENCES := 1;

ELSE

vP_CHECKREFERENCES := 0;

END IF;

EXCEPTION WHEN OTHERS THEN

vP_ERROR := 1;

raise notice ‘Select error from STRUCTURES’;

END;

$$ LANGUAGE plpgsql;

This guide proved that migrating stored procedures and functions from SQL Server to PostgreSQL is a complicated task. It can take a lot of time and efforts when doing manually. Fortunately, there is special software to automate the process of code migration fully or partially. One of such solutions can be downloaded here: https://www.convert-in.com/m2pcode.htm

Social

PINTEREST

LINKEDIN

Thanks for the support