-- 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');
SELECT * FROM vendas.cliente ORDER BY id;
SELECT synonym, target, status FROM synonym.list ORDER BY synonym;

-- 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;
SELECT obj_description('vendas.cliente'::regclass, 'pg_class');

-- unqualified names: synonym in current_schema(), target through search_path
SET search_path = vendas, erp;
SELECT synonym.create('cli', 'clientes');
SELECT synonym, target, status FROM synonym.list ORDER BY synonym;
SELECT count(*) FROM cli;
SELECT synonym.drop('cli');
RESET search_path;

-- quoted identifiers
CREATE TABLE erp."Pedidos Antigos" (id int);
SELECT synonym.create('vendas."Pedidos"', 'erp."Pedidos Antigos"');
SELECT synonym, target, status FROM synonym.list WHERE synonym LIKE '%Pedidos%';
SELECT synonym.drop('vendas."Pedidos"');

-- errors
SELECT synonym.create('vendas.cliente', 'erp.clientes');            -- already exists
SELECT synonym.create('erp.clientes', 'vendas.cliente');            -- name taken by a table
SELECT synonym.create('vendas.x', 'vendas.cliente');                -- synonym of a synonym
SELECT synonym.create('vendas.x', 'vendas.x');                      -- itself
SELECT synonym.create('vendas.x', 'outrobanco.dbo.clientes');       -- cross-database
SELECT synonym.create('nao_existe.x', 'erp.clientes');              -- schema
SELECT synonym.create('a.b.c', 'erp.clientes');                     -- too many parts
CREATE SEQUENCE erp.seq;
SELECT synonym.create('vendas.seq', 'erp.seq');                     -- not a table
CREATE TEMP TABLE tmp (id int);
SELECT synonym.create('vendas.tmp', 'tmp');                         -- temporary
SELECT synonym.create(NULL, 'erp.clientes');
SELECT synonym.drop('vendas.nao_existe');
SELECT synonym.drop('vendas.nao_existe', if_exists => true);

-- dropping a synonym leaves the target alone
SELECT synonym.drop('vendas.cliente');
SELECT to_regclass('vendas.cliente') IS NULL AS synonym_gone, count(*) AS rows_still_there FROM erp.clientes;
SELECT count(*) FROM synonym.list;

-- 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');
DROP VIEW vendas.item;
SELECT to_regclass('vendas.item') AS still_there, count(*) AS synonyms FROM synonym.list;

-- 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');
SELECT synonym.create('vendas.item_futuro', 'erp.ainda_nao_existe');
RESET ROLE;
DROP OWNED BY regress_tmp;
SELECT synonym, status FROM synonym.list ORDER BY synonym;
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);
SELECT count(*) AS synonyms FROM synonym.list;

DROP SCHEMA erp CASCADE;
DROP SCHEMA vendas CASCADE;