-- 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');

-- new column
ALTER TABLE erp.clientes ADD COLUMN email text DEFAULT 'sem@email';
SELECT * FROM vendas.cliente;

-- renamed column
ALTER TABLE erp.clientes RENAME COLUMN ativo TO is_active;
SELECT * FROM vendas.cliente;

-- 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;

-- 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;

-- dropped column (also blocked by a plain view)
ALTER TABLE erp.clientes DROP COLUMN email;
SELECT * FROM vendas.cliente;

-- several changes in one statement
ALTER TABLE erp.clientes ADD COLUMN cidade text, DROP COLUMN is_active;
SELECT * FROM vendas.cliente;

-- renamed and moved targets are followed, like a view would
ALTER TABLE erp.clientes RENAME TO customers;
SELECT synonym, target, status FROM synonym.list;
CREATE SCHEMA arquivo;
ALTER TABLE erp.customers SET SCHEMA arquivo;
SELECT synonym, target, status FROM synonym.list;
SELECT * FROM vendas.cliente;

-- 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;

-- ...but blocks changes that would destroy it, just like in plain PostgreSQL
ALTER TABLE arquivo.customers ALTER COLUMN nome TYPE varchar(100);
SELECT synonym, target, status FROM synonym.list;
-- DROP COLUMN ... CASCADE takes the dependent view along, and the synonym is rebuilt
ALTER TABLE arquivo.customers DROP COLUMN uf CASCADE;
SELECT to_regclass('vendas.relatorio') AS relatorio;
SELECT * FROM vendas.cliente;
-- now the type change goes through
ALTER TABLE arquivo.customers ALTER COLUMN nome TYPE varchar(100);
SELECT pg_typeof(nome) FROM vendas.cliente;

-- changes roll back cleanly
BEGIN;
ALTER TABLE arquivo.customers DROP COLUMN municipio;
SELECT * FROM vendas.cliente;
ROLLBACK;
SELECT * FROM vendas.cliente;

-- dropping the synonym's own schema drops the synonym, like any object in it
CREATE SCHEMA temporario;
SELECT synonym.create('temporario.c', 'arquivo.customers');
DROP SCHEMA temporario CASCADE;
SELECT synonym, target, status FROM synonym.list;

SELECT synonym.drop('vendas.cliente');
DROP SCHEMA erp CASCADE;
DROP SCHEMA arquivo CASCADE;
DROP SCHEMA vendas CASCADE;