Migrate stored procedures and functions from SQL Server to PostgreSQL
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:
- Unlike SQL Server, In PostgreSQL ‘DECLARE’ keyword must be used at the begin of variables definition section
- Output parameters must be transformed from ‘name type OUT’ into ‘OUTPUT name type’, optional keyword ‘AS’ between name and type must be omitted
- Symbol ‘@’ in front of every variable/parameter name must be removed or replaced by ‘v’ to indicate it is a variable/parameter name
- RETURN clause within a function prototype must be converted into ‘RETURNS’
- PostgreSQL requires language specification at the end of each function using ‘$$ LANGUAGE plpgsql;’ pattern
- 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, …);
- 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
