Synonyms are objects that provide alternative names for database objects. A synonym allows you to reference an object without the need to use the fully qualified name of the object, which includes the schema name and the object name. You can reference database objects in a simplified manner by using synonyms. For example, you can use synonyms in scenarios in which fully qualified object names are required in SQL statements.
In a PolarDB for PostgreSQL (Compatible with Oracle) cluster, you can configure synonyms for the following objects:
Tables
Views
Materialized views
Sequences
Procedures
Functions
Custom types
Packages
Objects that can be accessed by using a database link
The referenced schema or object may exist or not exist at the time when you create a synonym. A synonym may reference a non-existent object or schema. A synonym becomes invalid after you remove the referenced object or schema. To remove a synonym, you must explicitly remove it.
Similar to other schema objects, PolarDB for PostgreSQL (Compatible with Oracle) uses the search path to resolve unqualified synonym names. If you have two synonyms that have the same name, an unqualified reference to a synonym resolves to the first synonym with the specified name in the search path. If public is in your search path, you can reference a synonym in the schema without the need to qualify the name.
When a PolarDB for PostgreSQL (Compatible with Oracle) cluster executes an SQL statement that uses a synonym, the permissions of the current user are checked based on the underlying database object of the synonym. If the user does not have the required permissions for the object, the SQL statement fails to be executed.
Create a synonym
To create a synonym, execute the CREATE SYNONYM statement. Syntax:
CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema.]syn_name
FOR object_schema.object_name[@dblink_name];Table 1. Parameters
Parameter | Description |
syn_name | The name of the synonym. A synonym name must be unique within a schema. |
schema | The name of the schema in which the synonym is created. If you do not specify a schema name, the synonym is created in the first existing schema in your search path. |
object_name | The name of the object for which the synonym is created. |
object_schema | The name of the schema in which the object resides. |
dblink_name | The name of the database link. If the object is accessed by using a database link, configure this parameter. |
To replace an existing synonym with a new synonym, use the REPLACE clause in the statement.
To create a synonym in the public schema, use the PUBLIC clause in the statement. The CREATE PUBLIC SYNONYM statement that is compatible with Oracle databases creates a synonym in the public schema. Syntax:
CREATE [OR REPLACE] PUBLIC SYNONYM syn_name FOR object_schema.object_name;Shorthand syntax:
CREATE [OR REPLACE] SYNONYM public.syn_name FOR object_schema.object_name;In the following example, a synonym named personnel is created to reference the polardb.emp table.
CREATE SYNONYM personnel FOR polardb.emp;Unless the synonym is schema qualified in the CREATE SYNONYM statement, the synonym is created in the first existing schema in your search path. You can view your search path by executing the following statement:
SHOW SEARCH_PATH;
search_path
-----------------------
development,accounting
(1 row)In this example, if a schema named development does not exist, the synonym is created in the schema named accounting.
The emp table in the polardb schema can be referenced in any DDL or DML SQL statement by using the personnel synonym.
INSERT INTO personnel VALUES (8142,'ANDERSON','CLERK',7902,'17-DEC-06',1300,NULL,20);
SELECT * FROM personnel;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+----------+-----------+------+--------------------+---------+---------+--------
7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20
7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30
7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 | 30
7566 | JONES | MANAGER | 7839 | 02-APR-81 00:00:00 | 2975.00 | | 20
7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | 30
7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 00:00:00 | 2850.00 | | 30
7782 | CLARK | MANAGER | 7839 | 09-JUN-81 00:00:00 | 2450.00 | | 10
7788 | SCOTT | ANALYST | 7566 | 19-APR-87 00:00:00 | 3000.00 | | 20
7839 | KING | PRESIDENT | | 17-NOV-81 00:00:00 | 5000.00 | | 10
7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 | 0.00 | 30
7876 | ADAMS | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 | | 20
7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 | | 30
7902 | FORD | ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000.00 | | 20
7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | 10
8142 | ANDERSON | CLERK | 7902 | 17-DEC-06 00:00:00 | 1300.00 | | 20
(15 rows)Remove a synonym
To remove a synonym, execute the DROP SYNONYM statement. Syntax:
DROP [PUBLIC] SYNONYM [schema.]syn_nameTable 2. Parameters
Parameter | Description |
syn_name | The name of the synonym. A synonym name must be unique within a schema. |
schema | The name of the schema in which the synonym is created. |
Similar to other objects that can be schema-qualified, you can create two synonyms that have the same name in your search path. To distinguish the synonym that you want to remove, include the schema name. Unless a synonym is schema qualified in the DROP SYNONYM statement, the PolarDB for PostgreSQL (Compatible with Oracle) cluster removes the first instance of the synonym found in your search path.
To remove a synonym from the public schema, include the PUBLIC clause in the statement. The DROP PUBLIC SYNONYM statement that is compatible with Oracle databases removes a synonym from the public schema. Syntax:
DROP PUBLIC SYNONYM syn_name;In the following example, the synonym named personnel is removed:
DROP SYNONYM personnel;