SQL Server and PostgreSQL database management systems have similar syntax of stored procedures. However, there is number of particularities that must be handled to convert SQL properly from SQL Server to PostgreSQL. This article explores such differences.
Procedural Language. SQL Server can only use Transact-SQL (T-SQL) as its procedural language for creating stored procedures, functions and triggers.PostgreSQL supports multiple procedural languages, including PL/pgSQL (similar to T-SQL), PL/SQL, PL/Tcl, PL/Perl, and more. PL/pgSQL is commonly used for creating stored procedures.
Structure of Code.SQL Server allows to avoid BEGIN and END margins of the procedure, function or trigger body. PostgreSQL requires those operators are always present in the SQL code. The same is related to blocks of code in IF-THEN-ELSE conditional statements. SQL Server may use service statements to configure execution of the particular procedure, function or trigger:
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
PostgreSQL does not support this feature and such statements must be removed while convert SQL code.
Variables Declaration. SQL Server applies less strict limitations to declaring variables than PostgreSQL. In SQL Server variables can be declared literally anywhere inside the body of stored procedure, function or trigger. On the other hand, PostgreSQL requires that declaration block is located before begin of the body:
DECLARE
Var1 int;
Var2 text;
…
BEGIN
…
END;
This difference must be handled properly when convert SQL from MS SQL to PostgreSQL.
Triggers.
The source code of SQL Server’s trigger is located after CREATE TRIGGER statement. In contrast, trigger’s bodyin PostgreSQL must be composed as a distinct procedure referenced from CREATE TRIGGER statement. This is a key point to convert trigger’s SQL code from SQL Server to PostgreSQL.
Also, in SQL Server dedicated tables named “inserted” and “deleted” store inserted/updated new values and deleted/updated old values. Trigger reads those tables to process all rows being inserted, updated or deleted. PostgreSQL operates in other way since it does not have such tables. It provides FOR EACH ROW statement to explicitly iterate old and new values for insert, update or delete operations. Data from inserted or updated (new value) row can be accessed in PostgreSQL as NEW.column_name. Data from deleted or updated (old value) row can be accessed as OLD.column_name.
The following example illustrates how to convert SQL code of trigger from SQL Server to PostgreSQL. In SQL Server:
CREATE TRIGGER [dbo].[UpdateIDTrigger]
ON dbo.Reports AFTER INSERT, UPDATE
AS
BEGIN
UPDATE dbo.Reported
SET Reported.AssignedPerson = INSERTED.CreatedBy
FROM
dbo.Reported
INNER JOIN INSERTED ON Reported.ID = INSERTED.ID
WHERE Reported.ReportCount = 0
END
And the same trigger in PostgreSQL looks like:
CREATE FUNCTION UpdateID_proc() RETURNS trigger AS $$
BEGIN
UPDATE public.Reported
SET Reported.AssignedPerson = NEW.CreatedBy
WHERE Reported.ReportCount = 0 AND Reported. ID = NEW.ID;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER UpdateIDTrigger
AFTER INSERT OR UPDATE ON public.Reports
FOR EACH ROW EXECUTE PROCEDURE UpdateID_proc();
The steps described above prove that it is quite complicated task to convert SQL code from SQL Server to PostgreSQL. Fortunately, there are software tools designed to simplify and partially automate this procedure. One of these tools is SQL Server to PostgreSQL Code Converter developed by Intelligent Converters software company.
