SQL Server Synonyms and PostgreSQL Views, Types, and Functions - SQL Server to Aurora PostgreSQL Migration Playbook
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China.

SQL Server Synonyms and PostgreSQL Views, Types, and Functions

Feature compatibility Amazon SCT / Amazon DMS automation level Amazon SCT action code index Key differences


                              Two star feature compatibility


                              No automation

N/A

PostgreSQL doesn’t support synonyms. There is an available workaround.

SQL Server Usage

Synonyms are database objects that serve as alternative identifiers for other database objects. The referenced database object is called the base object and may reside in the same database, another database on the same instance, or a remote server.

Synonyms provide an abstraction layer to isolate client application code from changes to the name or location of the base object.

In SQL Server, synonyms are often used to simplify the use of four-part identifiers when accessing remote instances.

For example, the table A resides on the server A, and the client application accesses it directly. For scale out reasons, the table A needs to be moved to the server B to offload resource consumption on the server A. Without synonyms, the client application code must be rewritten to access the server B. Instead, you can create a synonym called Table A and it will transparently redirect the calling application to the server B without any code changes.

You can create synonyms for the following objects:

  • Assembly (CLR) stored procedures, table-valued functions, scalar functions, and aggregate functions.

  • Replication-filter-procedures.

  • Extended stored procedures.

  • SQL scalar functions, table-valued functions, inline-tabled-valued functions, views, and stored procedures.

  • User-defined tables including local and global temporary tables.

Syntax

CREATE SYNONYM [ <Synonym Schema> ] . <Synonym Name>
FOR [ <Server Name> ] . [ <Database Name> ] . [ Schema Name> ] . <Object Name>

Examples

The following example creates a synonym for a local object in a separate database.

CREATE TABLE DB1.Schema1.MyTable
(
KeyColumn INT IDENTITY PRIMARY KEY,
DataColumn VARCHAR(20) NOT NULL
);

USE DB2;
CREATE SYNONYM Schema2.MyTable
FOR DB1.Schema1.MyTable

The following example creates a synonym for a remote object.

-- On ServerA
CREATE TABLE DB1.Schema1.MyTable
(
KeyColumn INT IDENTITY PRIMARY KEY,
DataColumn VARCHAR(20) NOT NULL
);

-- On Server B
USE DB2;
CREATE SYNONYM Schema2.MyTable
FOR ServerA.DB1.Schema1.MyTable;

The example preceding assumes a linked server named ServerA exists on Server B that points to Server A.

For more information, see CREATE SYNONYM (Transact-SQL) in the SQL Server documentation.

PostgreSQL Usage

SQL Server synonyms are often used to give another name for an object. PostgreSQL doesn’t provide a feature comparable to SQL Server Synonyms. However, you can achieve similar functionality by using a few PostgreSQL objects.

This lack of functionality will add a manual dimension to migration process wherever SQL Server Synonyms are involved. The user using these objects must have privileges on the base object and relevant PostgreSQL options should be used.

Examples

To create a synonym of a table in PostgreSQL, use views.

The first step is to create a table that will be used as the base object, and on top of it, a view that will be used as synonym.

CREATE TABLE DB1.Schema1.MyTable
(
  KeyColumn NUMERIC PRIMARY KEY,
  DataColumn VARCHAR(20) NOT NULL
);

CREATE VIEW DB2.Schema2.MyTable_Syn
AS SELECT * FROM DB1.Schema1.MyTable

For more information, see Views.

To create a synonym of a user-defined type in PostgreSQL, another user-defined type should be used to wrap the source type.

The first step is to create the user-defined type that will be used as the base object, and on top of it, a user-defined type that will be used as the synonym.

CREATE TYPE DB1.Schema1.MyType AS (
ID NUMERIC,
name CHARACTER VARYING(100));

CREATE TYPE DB2.Schema2.MyType_Syn AS (
udt DB1.Schema1.MyT);

For more information, see User-Defined Types.

To create a synonym for a function in PostgreSQL, another function should be used to wrap the source type.

As before, the first step is to create the function that will be used as the base object. And then, on top of it, create a function that will be used as the synonym.

CREATE OR REPLACE FUNCTION DB1.Schema1.MyFunc (P_NUM NUMERIC)
RETURNS numeric AS $$
begin
  RETURN P_NUM * 2;
END; $$
LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION DB2.Schema2.MyFunc_Syn (P_NUM NUMERIC)
RETURNS numeric AS $$
begin
  RETURN DB1.Schema1.MyFunc(P_NUM);
END; $$
LANGUAGE PLPGSQL;

For more information, see User-Defined Functions.