-- The synonym follows every change made to its target
\pset null '(null)'
\set SHOW_CONTEXT never
CREATE SCHEMA erp;
CREATE SCHEMA vendas;
CREATE TABLE erp.clientes (id int PRIMARY KEY, nome text, ativo boolean);
INSERT INTO erp.clientes VALUES (1, 'Ana', true);
SELECT synonym.create('vendas.cliente', 'erp.clientes');
 create 
--------
 
(1 row)

-- new column
ALTER TABLE erp.clientes ADD COLUMN email text DEFAULT 'sem@email';
SELECT * FROM vendas.cliente;
 id | nome | ativo |   email   
----+------+-------+-----------
  1 | Ana  | t     | sem@email
(1 row)

-- renamed column
ALTER TABLE erp.clientes RENAME COLUMN ativo TO is_active;
SELECT * FROM vendas.cliente;
 id | nome | is_active |   email   
----+------+-----------+-----------
  1 | Ana  | t         | sem@email
(1 row)

-- two columns swapping names
ALTER TABLE erp.clientes RENAME COLUMN nome TO tmp;
ALTER TABLE erp.clientes RENAME COLUMN email TO nome;
ALTER TABLE erp.clientes RENAME COLUMN tmp TO email;
SELECT * FROM vendas.cliente;
 id | email | is_active |   nome    
----+-------+-----------+-----------
  1 | Ana   | t         | sem@email
(1 row)

-- changed type (blocked by a plain view; pg_synonym handles it)
ALTER TABLE erp.clientes ALTER COLUMN id TYPE bigint;
SELECT pg_typeof(id) FROM vendas.cliente;
 pg_typeof 
-----------
 bigint
(1 row)

-- dropped column (also blocked by a plain view)
ALTER TABLE erp.clientes DROP COLUMN email;
SELECT * FROM vendas.cliente;
 id | is_active |   nome    
----+-----------+-----------
  1 | t         | sem@email
(1 row)

-- several changes in one statement
ALTER TABLE erp.clientes ADD COLUMN cidade text, DROP COLUMN is_active;
SELECT * FROM vendas.cliente;
 id |   nome    | cidade 
----+-----------+--------
  1 | sem@email | (null)
(1 row)

-- renamed and moved targets are followed, like a view would
ALTER TABLE erp.clientes RENAME TO customers;
SELECT synonym, target, status FROM synonym.list;
    synonym     |    target     | status 
----------------+---------------+--------
 vendas.cliente | erp.customers | ok
(1 row)

CREATE SCHEMA arquivo;
ALTER TABLE erp.customers SET SCHEMA arquivo;
SELECT synonym, target, status FROM synonym.list;
    synonym     |      target       | status 
----------------+-------------------+--------
 vendas.cliente | arquivo.customers | ok
(1 row)

SELECT * FROM vendas.cliente;
 id |   nome    | cidade 
----+-----------+--------
  1 | sem@email | (null)
(1 row)

-- a view the user built on top of the synonym survives harmless changes...
CREATE VIEW vendas.relatorio AS SELECT id, nome FROM vendas.cliente;
ALTER TABLE arquivo.customers ADD COLUMN uf char(2);
ALTER TABLE arquivo.customers RENAME COLUMN cidade TO municipio;
SELECT * FROM vendas.relatorio;
 id |   nome    
----+-----------
  1 | sem@email
(1 row)

-- ...but blocks changes that would destroy it, just like in plain PostgreSQL
ALTER TABLE arquivo.customers ALTER COLUMN nome TYPE varchar(100);
ERROR:  other objects depend on synonym vendas.cliente
DETAIL:  The synonym must be rebuilt for this command, and the objects that use it would be lost.
HINT:  Drop those objects first, or use CASCADE if the command accepts it (DROP TABLE and DROP COLUMN do).
SELECT synonym, target, status FROM synonym.list;
    synonym     |      target       | status 
----------------+-------------------+--------
 vendas.cliente | arquivo.customers | ok
(1 row)

-- DROP COLUMN ... CASCADE takes the dependent view along, and the synonym is rebuilt
ALTER TABLE arquivo.customers DROP COLUMN uf CASCADE;
NOTICE:  drop cascades to view vendas.relatorio
SELECT to_regclass('vendas.relatorio') AS relatorio;
 relatorio 
-----------
 (null)
(1 row)

SELECT * FROM vendas.cliente;
 id |   nome    | municipio 
----+-----------+-----------
  1 | sem@email | (null)
(1 row)

-- now the type change goes through
ALTER TABLE arquivo.customers ALTER COLUMN nome TYPE varchar(100);
SELECT pg_typeof(nome) FROM vendas.cliente;
     pg_typeof     
-------------------
 character varying
(1 row)

-- changes roll back cleanly
BEGIN;
ALTER TABLE arquivo.customers DROP COLUMN municipio;
SELECT * FROM vendas.cliente;
 id |   nome    
----+-----------
  1 | sem@email
(1 row)

ROLLBACK;
SELECT * FROM vendas.cliente;
 id |   nome    | municipio 
----+-----------+-----------
  1 | sem@email | (null)
(1 row)

-- dropping the synonym's own schema drops the synonym, like any object in it
CREATE SCHEMA temporario;
SELECT synonym.create('temporario.c', 'arquivo.customers');
 create 
--------
 
(1 row)

DROP SCHEMA temporario CASCADE;
NOTICE:  drop cascades to view temporario.c
SELECT synonym, target, status FROM synonym.list;
    synonym     |      target       | status 
----------------+-------------------+--------
 vendas.cliente | arquivo.customers | ok
(1 row)

SELECT synonym.drop('vendas.cliente');
 drop 
------
 
(1 row)

DROP SCHEMA erp CASCADE;
DROP SCHEMA arquivo CASCADE;
NOTICE:  drop cascades to table arquivo.customers
DROP SCHEMA vendas CASCADE;