-- Creating, listing and dropping synonyms
CREATE EXTENSION pg_synonym;
\pset null '(null)'
\set SHOW_CONTEXT never
CREATE SCHEMA erp;
CREATE SCHEMA vendas;
CREATE TABLE erp.clientes (id int PRIMARY KEY, nome text NOT NULL);
INSERT INTO erp.clientes VALUES (1, 'Ana'), (2, 'Bia');
-- the SQL Server way would be: CREATE SYNONYM vendas.cliente FOR erp.clientes
SELECT synonym.create('vendas.cliente', 'erp.clientes');
 create 
--------
 
(1 row)

SELECT * FROM vendas.cliente ORDER BY id;
 id | nome 
----+------
  1 | Ana
  2 | Bia
(2 rows)

SELECT synonym, target, status FROM synonym.list ORDER BY synonym;
    synonym     |    target    | status 
----------------+--------------+--------
 vendas.cliente | erp.clientes | ok
(1 row)

-- it is a view with security_invoker, owned by the creator, readable through the target's permissions
SELECT c.relkind, c.reloptions FROM pg_class c WHERE c.oid = 'vendas.cliente'::regclass;
 relkind |       reloptions        
---------+-------------------------
 v       | {security_invoker=true}
(1 row)

SELECT obj_description('vendas.cliente'::regclass, 'pg_class');
                 obj_description                  
--------------------------------------------------
 Synonym for erp.clientes (managed by pg_synonym)
(1 row)

-- unqualified names: synonym in current_schema(), target through search_path
SET search_path = vendas, erp;
SELECT synonym.create('cli', 'clientes');
 create 
--------
 
(1 row)

SELECT synonym, target, status FROM synonym.list ORDER BY synonym;
    synonym     |    target    | status 
----------------+--------------+--------
 vendas.cli     | erp.clientes | ok
 vendas.cliente | erp.clientes | ok
(2 rows)

SELECT count(*) FROM cli;
 count 
-------
     2
(1 row)

SELECT synonym.drop('cli');
 drop 
------
 
(1 row)

RESET search_path;
-- quoted identifiers
CREATE TABLE erp."Pedidos Antigos" (id int);
SELECT synonym.create('vendas."Pedidos"', 'erp."Pedidos Antigos"');
 create 
--------
 
(1 row)

SELECT synonym, target, status FROM synonym.list WHERE synonym LIKE '%Pedidos%';
     synonym      |        target         | status 
------------------+-----------------------+--------
 vendas."Pedidos" | erp."Pedidos Antigos" | ok
(1 row)

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

-- errors
SELECT synonym.create('vendas.cliente', 'erp.clientes');            -- already exists
ERROR:  synonym "vendas.cliente" already exists
SELECT synonym.create('erp.clientes', 'vendas.cliente');            -- name taken by a table
ERROR:  relation "erp.clientes" already exists
SELECT synonym.create('vendas.x', 'vendas.cliente');                -- synonym of a synonym
ERROR:  "vendas.cliente" is itself a synonym
HINT:  A synonym must point at a real table or view.
SELECT synonym.create('vendas.x', 'vendas.x');                      -- itself
ERROR:  a synonym cannot point at itself
SELECT synonym.create('vendas.x', 'outrobanco.dbo.clientes');       -- cross-database
ERROR:  cross-database synonyms are not supported: outrobanco.dbo.clientes
HINT:  Create a foreign table with postgres_fdw and point the synonym at it.
SELECT synonym.create('nao_existe.x', 'erp.clientes');              -- schema
ERROR:  schema "nao_existe" does not exist
SELECT synonym.create('a.b.c', 'erp.clientes');                     -- too many parts
ERROR:  improper synonym name (too many dotted names): a.b.c
HINT:  Use schema.name or just name.
CREATE SEQUENCE erp.seq;
SELECT synonym.create('vendas.seq', 'erp.seq');                     -- not a table
ERROR:  "erp.seq" is not a table, view, materialized view or foreign table
CREATE TEMP TABLE tmp (id int);
SELECT synonym.create('vendas.tmp', 'tmp');                         -- temporary
ERROR:  a synonym cannot point at a temporary table
SELECT synonym.create(NULL, 'erp.clientes');
ERROR:  synonym name and target must not be NULL
SELECT synonym.drop('vendas.nao_existe');
ERROR:  synonym "vendas.nao_existe" does not exist
SELECT synonym.drop('vendas.nao_existe', if_exists => true);
NOTICE:  synonym "vendas.nao_existe" does not exist, skipping
 drop 
------
 
(1 row)

-- dropping a synonym leaves the target alone
SELECT synonym.drop('vendas.cliente');
 drop 
------
 
(1 row)

SELECT to_regclass('vendas.cliente') IS NULL AS synonym_gone, count(*) AS rows_still_there FROM erp.clientes;
 synonym_gone | rows_still_there 
--------------+------------------
 t            |                2
(1 row)

SELECT count(*) FROM synonym.list;
 count 
-------
     0
(1 row)

-- DROP VIEW on a synonym's view is the same as synonym.drop()
CREATE TABLE erp.itens (id int);
SELECT synonym.create('vendas.item', 'erp.itens');
 create 
--------
 
(1 row)

DROP VIEW vendas.item;
SELECT to_regclass('vendas.item') AS still_there, count(*) AS synonyms FROM synonym.list;
 still_there | synonyms 
-------------+----------
 (null)      |        0
(1 row)

-- so is DROP OWNED BY the synonym's owner
CREATE ROLE regress_tmp;
GRANT CREATE, USAGE ON SCHEMA vendas TO regress_tmp;
GRANT USAGE ON SCHEMA erp TO regress_tmp;
SET ROLE regress_tmp;
SELECT synonym.create('vendas.item', 'erp.itens');
 create 
--------
 
(1 row)

SELECT synonym.create('vendas.item_futuro', 'erp.ainda_nao_existe');
NOTICE:  "erp.ainda_nao_existe" does not exist yet; synonym "vendas.item_futuro" will start working as soon as it is created
 create 
--------
 
(1 row)

RESET ROLE;
DROP OWNED BY regress_tmp;
SELECT synonym, status FROM synonym.list ORDER BY synonym;
      synonym       | status  
--------------------+---------
 vendas.item_futuro | pending
(1 row)

DROP ROLE regress_tmp;
-- a pending synonym whose owner is gone is removed when its target appears
CREATE TABLE erp.ainda_nao_existe (id int);
WARNING:  pg_synonym: removed synonym vendas.item_futuro because its owner "regress_tmp" no longer exists
SELECT count(*) AS synonyms FROM synonym.list;
 synonyms 
----------
        0
(1 row)

DROP SCHEMA erp CASCADE;
NOTICE:  drop cascades to 5 other objects
DETAIL:  drop cascades to table erp.clientes
drop cascades to table erp."Pedidos Antigos"
drop cascades to sequence erp.seq
drop cascades to table erp.itens
drop cascades to table erp.ainda_nao_existe
DROP SCHEMA vendas CASCADE;