-- 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');
NOTICE:  "erp.produtos" does not exist yet; synonym "vendas.produto" will start working as soon as it is created
 create 
--------
 
(1 row)

SELECT synonym, target, status FROM synonym.list;
    synonym     |    target    | status  
----------------+--------------+---------
 vendas.produto | erp.produtos | pending
(1 row)

SELECT * FROM vendas.produto;   -- not usable yet
ERROR:  relation "vendas.produto" does not exist
LINE 1: SELECT * FROM vendas.produto;
                      ^
-- 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;
    synonym     |    target    | status 
----------------+--------------+--------
 vendas.produto | erp.produtos | ok
(1 row)

SELECT * FROM vendas.produto;
 sku | preco 
-----+-------
 A1  |    10
(1 row)

-- dropping the target does not need CASCADE and keeps the synonym
DROP TABLE erp.produtos;
SELECT synonym, target, status FROM synonym.list;
    synonym     |    target    | status  
----------------+--------------+---------
 vendas.produto | erp.produtos | pending
(1 row)

-- 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;
 codigo | descricao 
--------+-----------
 B2     | Caneta
(1 row)

-- 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;
 codigo 
--------
 C3
(1 row)

DROP VIEW erp.produtos;
SELECT synonym, target, status FROM synonym.list;
    synonym     |    target    | status  
----------------+--------------+---------
 vendas.produto | erp.produtos | pending
(1 row)

-- the target's schema can go and come back
SELECT synonym.create('vendas.nota', 'fiscal.notas');
NOTICE:  "fiscal.notas" does not exist yet; synonym "vendas.nota" will start working as soon as it is created
 create 
--------
 
(1 row)

CREATE SCHEMA fiscal;
CREATE TABLE fiscal.notas (numero int);
SELECT synonym, target, status FROM synonym.list ORDER BY synonym;
    synonym     |    target    | status  
----------------+--------------+---------
 vendas.nota    | fiscal.notas | ok
 vendas.produto | erp.produtos | pending
(2 rows)

DROP SCHEMA fiscal CASCADE;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to table fiscal.notas
drop cascades to view vendas.nota
SELECT synonym, target, status FROM synonym.list ORDER BY synonym;
    synonym     |    target    | status  
----------------+--------------+---------
 vendas.nota    | fiscal.notas | pending
 vendas.produto | erp.produtos | pending
(2 rows)

-- 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
ERROR:  other objects depend on synonym vendas.produto
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).
DROP TABLE erp.produtos CASCADE;
NOTICE:  drop cascades to view vendas.vitrine
SELECT to_regclass('vendas.vitrine') AS vitrine;
 vitrine 
---------
 (null)
(1 row)

SELECT synonym, target, status FROM synonym.list ORDER BY synonym;
    synonym     |    target    | status  
----------------+--------------+---------
 vendas.nota    | fiscal.notas | pending
 vendas.produto | erp.produtos | pending
(2 rows)

-- someone takes the synonym's name while it is pending
CREATE TABLE vendas.produto (outra_coisa text);
WARNING:  pg_synonym: could not update synonym vendas.produto: relation "vendas.produto" already exists and is not managed by pg_synonym
SELECT synonym, target, status FROM synonym.list ORDER BY synonym;
    synonym     |    target    |  status  
----------------+--------------+----------
 vendas.nota    | fiscal.notas | pending
 vendas.produto | erp.produtos | conflict
(2 rows)

CREATE TABLE erp.produtos (sku text);    -- warns, but the CREATE TABLE still works
WARNING:  pg_synonym: could not update synonym vendas.produto: relation "vendas.produto" already exists and is not managed by pg_synonym
SELECT to_regclass('erp.produtos') IS NOT NULL AS created;
 created 
---------
 t
(1 row)

-- once the name is free again, the synonym takes it
DROP TABLE vendas.produto;
SELECT synonym, target, status FROM synonym.list ORDER BY synonym;
    synonym     |    target    | status  
----------------+--------------+---------
 vendas.nota    | fiscal.notas | pending
 vendas.produto | erp.produtos | ok
(2 rows)

SELECT * FROM vendas.produto;
 sku 
-----
(0 rows)

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

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

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