-- Synonyms whose target does not exist (yet, or anymore)
\pset null '(null)'
\set SHOW_CONTEXT never

CREATE SCHEMA erp;
CREATE SCHEMA vendas;

-- created before its target, like in SQL Server
SELECT synonym.create('vendas.produto', 'erp.produtos');
SELECT synonym, target, status FROM synonym.list;
SELECT * FROM vendas.produto;   -- not usable yet

-- comes alive when the target appears
CREATE TABLE erp.produtos (sku text, preco numeric);
INSERT INTO erp.produtos VALUES ('A1', 10);
SELECT synonym, target, status FROM synonym.list;
SELECT * FROM vendas.produto;

-- dropping the target does not need CASCADE and keeps the synonym
DROP TABLE erp.produtos;
SELECT synonym, target, status FROM synonym.list;

-- a new object with the same name brings it back, with the new columns
CREATE TABLE erp.produtos AS SELECT 'B2'::text AS codigo, 'Caneta'::text AS descricao;
SELECT * FROM vendas.produto;

-- the target can be a view created later, too
DROP TABLE erp.produtos;
CREATE VIEW erp.produtos AS SELECT 'C3'::text AS codigo;
SELECT * FROM vendas.produto;
DROP VIEW erp.produtos;
SELECT synonym, target, status FROM synonym.list;

-- the target's schema can go and come back
SELECT synonym.create('vendas.nota', 'fiscal.notas');
CREATE SCHEMA fiscal;
CREATE TABLE fiscal.notas (numero int);
SELECT synonym, target, status FROM synonym.list ORDER BY synonym;
DROP SCHEMA fiscal CASCADE;
SELECT synonym, target, status FROM synonym.list ORDER BY synonym;

-- DROP TABLE ... CASCADE also drops what was built on the synonym
CREATE TABLE erp.produtos (sku text);
CREATE VIEW vendas.vitrine AS SELECT sku FROM vendas.produto;
DROP TABLE erp.produtos;                 -- refused: vendas.vitrine uses it
DROP TABLE erp.produtos CASCADE;
SELECT to_regclass('vendas.vitrine') AS vitrine;
SELECT synonym, target, status FROM synonym.list ORDER BY synonym;

-- someone takes the synonym's name while it is pending
CREATE TABLE vendas.produto (outra_coisa text);
SELECT synonym, target, status FROM synonym.list ORDER BY synonym;
CREATE TABLE erp.produtos (sku text);    -- warns, but the CREATE TABLE still works
SELECT to_regclass('erp.produtos') IS NOT NULL AS created;
-- once the name is free again, the synonym takes it
DROP TABLE vendas.produto;
SELECT synonym, target, status FROM synonym.list ORDER BY synonym;
SELECT * FROM vendas.produto;

SELECT synonym.drop('vendas.produto');
SELECT synonym.drop('vendas.nota');
DROP SCHEMA erp CASCADE;
DROP SCHEMA vendas CASCADE;