# pg_synonym

[![CI](https://github.com/Mazuco/pg_synonym/actions/workflows/ci.yml/badge.svg)](https://github.com/Mazuco/pg_synonym/actions/workflows/ci.yml)
![PostgreSQL 15+](https://img.shields.io/badge/PostgreSQL-15%2B-336791?logo=postgresql&logoColor=white)
[![License: PostgreSQL](https://img.shields.io/badge/license-PostgreSQL-blue.svg)](LICENSE)

**`CREATE SYNONYM` for PostgreSQL.** Give a table or view a second name in another schema, and keep using
it for reads and writes while the real table changes underneath.

🇧🇷 [Leia em português](README.pt-BR.md)

```sql
-- SQL Server:  CREATE SYNONYM vendas.cliente FOR erp.clientes
SELECT synonym.create('vendas.cliente', 'erp.clientes');

SELECT * FROM vendas.cliente;                       -- reads erp.clientes
INSERT INTO vendas.cliente (nome) VALUES ('Ana');   -- writes erp.clientes
```

## Why?

SQL Server and Oracle have synonyms. Applications use them to hide where a table really lives, which schema it
is in, or what it is called this year. PostgreSQL has no synonyms, so migrations usually fall back on one of two
workarounds:

- **A view.** It works until someone runs `ALTER TABLE` on the real table. New columns do not show up,
  `DROP COLUMN` and `ALTER COLUMN TYPE` are refused ("other objects depend on it"), and `DROP TABLE` needs
  `CASCADE`.
- **`search_path` tricks.** These are global, fragile and invisible in the code.

`pg_synonym` gives you real synonyms. Each one is backed by an updatable view, and the extension keeps that view
in step with its target automatically, whatever `ALTER TABLE` throws at it.

## Usage

### Create and drop

```sql
SELECT synonym.create('vendas.cliente', 'erp.clientes');
SELECT synonym.drop('vendas.cliente');
SELECT synonym.drop('vendas.cliente', if_exists => true);
```

`DROP VIEW vendas.cliente`, or `DROP OWNED BY` the synonym's owner, also drops the synonym.

Names follow the usual PostgreSQL rules. Without a schema, the synonym goes into the first schema of `search_path`,
and the target is looked up through `search_path`. Quoted names work: `synonym.create('app."Pedidos"', 'erp."Old Orders"')`.

### List

```sql
SELECT * FROM synonym.list;
```
```
    synonym     |    target     | owner |  status  |          created_at
----------------+---------------+-------+----------+-------------------------------
 vendas.cliente | erp.clientes  | ana   | ok       | 2026-10-19 15:47:28.03-03
 vendas.produto | erp.produtos  | ana   | pending  | 2026-10-19 15:48:02.11-03
```

| status | meaning |
|---|---|
| `ok` | Working. |
| `pending` | The target does not exist yet. The synonym starts working as soon as it is created. |
| `conflict` | Another object already uses the synonym's name. Rename or drop it. |
| `out of sync` | Should not happen. Run `SELECT synonym.refresh();`. |

### Use it like the table

```sql
SELECT ... FROM vendas.cliente JOIN ...;
INSERT INTO vendas.cliente (...) VALUES (...) RETURNING id;
INSERT ... ON CONFLICT (id) DO UPDATE ...;
UPDATE vendas.cliente SET ...;
DELETE FROM vendas.cliente WHERE ...;
```

The target's defaults, identity columns, constraints, triggers and row-level security all apply. The planner
sees straight through to the real table, so queries through a synonym are exactly as fast as queries on the
table.

### SQL Server → PostgreSQL cheat sheet

| SQL Server | pg_synonym |
|---|---|
| `CREATE SYNONYM s.x FOR s.y` | `SELECT synonym.create('s.x', 's.y')` |
| `DROP SYNONYM s.x` | `SELECT synonym.drop('s.x')` |
| `DROP SYNONYM IF EXISTS s.x` | `SELECT synonym.drop('s.x', if_exists => true)` |
| `SELECT * FROM sys.synonyms` | `SELECT * FROM synonym.list` |
| `CREATE SYNONYM x FOR otherdb.dbo.y` | Not supported. Create a foreign table with `postgres_fdw` and point the synonym at it |

## What it keeps in sync

| You do this to the target | The synonym |
|---|---|
| `ADD COLUMN` | shows the new column |
| `RENAME COLUMN` | uses the new name |
| `ALTER COLUMN ... TYPE` | uses the new type (a plain view would block this) |
| `DROP COLUMN` | loses the column (a plain view would block this) |
| `RENAME TO`, `SET SCHEMA` | keeps pointing at the same table, like a view would |
| `DROP TABLE` | stays defined and becomes `pending` (no `CASCADE` needed) |
| create it again | starts working again, with the new columns |

You can also create a synonym **before** its target exists (late binding, as in SQL Server). It becomes
`pending` and comes alive when the target is created.

Views that you build **on top of** a synonym survive harmless changes such as adding or renaming columns.
Changes that need the synonym rebuilt (`DROP COLUMN`, `ALTER COLUMN TYPE`, `DROP TABLE`) are refused while such
views exist, which is the same thing PostgreSQL does for any view. Use `CASCADE` where the command accepts it.

## Permissions

A synonym never gives anyone more access than they already have to the target.

- Every synonym view is created with `security_invoker = true`. The person **using** the synonym needs the
  privileges on the target, and the target's row-level security policies apply to them.
- To create a synonym you need `CREATE` privilege on the synonym's schema, just as for creating a view, and
  `USAGE` on the target's schema so you can name it. You do not need any privilege on the target table itself.
- Only the synonym's owner, a member of the owner's role, or a superuser can drop it.
- The table's owner can still `ALTER` or `DROP` the table even when other users have synonyms on it.

Installing the extension requires a superuser, because it uses event triggers. Using it does not.

## Installation

Requires **PostgreSQL 15 or newer**. There is nothing to add to `shared_preload_libraries` and no restart.

### Linux (Debian / Ubuntu)

```bash
sudo apt install build-essential postgresql-server-dev-17   # use your PostgreSQL major version
git clone https://github.com/Mazuco/pg_synonym.git
cd pg_synonym
make
sudo make install
```

### Linux (Rocky / RHEL / Fedora, PGDG packages)

```bash
sudo dnf install -y gcc make git redhat-rpm-config dnf-plugins-core
sudo dnf config-manager --set-enabled crb          # Rocky 9 (Rocky 8: --set-enabled powertools)
sudo dnf install -y postgresql17-devel
make PG_CONFIG=/usr/pgsql-17/bin/pg_config with_llvm=no
sudo make install PG_CONFIG=/usr/pgsql-17/bin/pg_config with_llvm=no
```

### macOS (Homebrew)

```bash
brew install postgresql@17
make PG_CONFIG=$(brew --prefix postgresql@17)/bin/pg_config
make install PG_CONFIG=$(brew --prefix postgresql@17)/bin/pg_config
```

### Docker (any OS, including Windows)

```bash
docker build -t pg_synonym .                    # PostgreSQL 17 by default
docker build --build-arg PG_MAJOR=18 -t pg_synonym:18 .
docker run -d --name pgsyn -e POSTGRES_PASSWORD=postgres -p 5432:5432 pg_synonym
```

> **Build error mentioning `clang` or `llvm`?** Your PostgreSQL package was built with JIT support and tries to
> compile LLVM bitcode, which this extension does not need. Build with `make with_llvm=no` and install with
> `sudo make with_llvm=no install`.

### Enable it in your database

```sql
CREATE EXTENSION pg_synonym;   -- as a superuser
```

The extension lives in its own schema, `synonym`.

## Limitations

- **Tables, views, materialized views, partitioned tables and foreign tables** can be targets. Functions,
  procedures and sequences cannot (yet).
- **No new syntax.** PostgreSQL extensions cannot add SQL statements, so `CREATE SYNONYM` is a function call.
- **No cross-database synonyms.** Point the synonym at a `postgres_fdw` foreign table instead.
- **A synonym of a synonym** is not allowed. SQL Server does not allow it either.
- **Things views cannot do** do not work through a synonym either: `TRUNCATE`, `COPY synonym TO` (use
  `COPY (SELECT * FROM synonym) TO`), and DDL such as `ALTER TABLE synonym`. `MERGE` into a synonym needs
  PostgreSQL 17 or newer.
- **Renamed targets are followed.** A synonym keeps pointing at its table after `RENAME` or `SET SCHEMA`, as a
  view would. SQL Server binds by name instead.
- **Manage synonyms only through these functions.** Renaming a synonym's view, its schema, or turning off
  `security_invoker` by hand breaks the link. When a synonym is rebuilt, GRANTs you added to it by hand are reset.
- **`DROP EXTENSION pg_synonym`** turns existing synonyms into ordinary views and leaves the `synonym` schema
  behind (`DROP SCHEMA synonym` removes it).

## Performance

- **Queries:** no overhead. A synonym is a simple view that the planner removes.
- **DDL:** the event triggers add work to `CREATE`, `ALTER` and `DROP` of tables and views. Measured on PostgreSQL
  16: about 0.4 ms per DDL with no synonyms, and about 11 ms per DDL with 500 synonyms.

## How it works

A synonym is a row in `synonym.catalog` plus a view `CREATE VIEW ... WITH (security_invoker = true) AS SELECT * FROM target`.
Event triggers keep them together:

1. **Before** `DROP TABLE`, `DROP COLUMN` or `ALTER COLUMN TYPE` on a target, a small C function reads the command,
   finds the synonyms involved and drops their views, so PostgreSQL does not refuse the command.
2. **After** any DDL on tables, views and schemas, a PL/pgSQL function compares each synonym with its target and
   creates, extends, renames or rebuilds the view as needed.
3. **When** someone drops a synonym's view directly (`DROP VIEW`, `DROP OWNED`), the synonym is forgotten.

If the command fails, the whole transaction rolls back, including step 1. Everything except about 200 lines of commented C is
plain PL/pgSQL in [`pg_synonym--1.0.sql`](pg_synonym--1.0.sql). A line-by-line walkthrough in Portuguese is in
[docs/COMO_FUNCIONA.md](docs/COMO_FUNCIONA.md).

## Running the tests

```bash
make
sudo make install
make installcheck          # needs a running server and a superuser connection
```

## Contributing

Issues and pull requests are welcome. Please add a regression test in `test/sql/` and its expected output in
`test/expected/` for any change in behaviour.

## License

Released under the [PostgreSQL License](LICENSE).