-- Reading and writing through a synonym
\pset null '(null)'
\set SHOW_CONTEXT never
CREATE SCHEMA erp;
CREATE SCHEMA vendas;
CREATE TABLE erp.clientes (
    id     int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nome   text NOT NULL,
    ativo  boolean DEFAULT true,
    limite numeric(10,2) CHECK (limite >= 0)
);
SELECT synonym.create('vendas.cliente', 'erp.clientes');
 create 
--------
 
(1 row)

-- INSERT uses the target's defaults and identity
INSERT INTO vendas.cliente (nome, limite) VALUES ('Ana', 100) RETURNING id, nome, ativo, limite;
 id | nome | ativo | limite 
----+------+-------+--------
  1 | Ana  | t     | 100.00
(1 row)

INSERT INTO vendas.cliente (nome) VALUES ('Bia'), ('Caio');
-- UPDATE / DELETE
UPDATE vendas.cliente SET ativo = false WHERE nome = 'Bia';
DELETE FROM vendas.cliente WHERE nome = 'Caio';
-- ON CONFLICT
INSERT INTO vendas.cliente (id, nome) OVERRIDING SYSTEM VALUE VALUES (1, 'Ana Maria')
ON CONFLICT (id) DO UPDATE SET nome = excluded.nome;
-- the target's constraints still apply
INSERT INTO vendas.cliente (nome, limite) VALUES ('Davi', -1);
ERROR:  new row for relation "clientes" violates check constraint "clientes_limite_check"
DETAIL:  Failing row contains (4, Davi, t, -1.00).
SELECT * FROM erp.clientes ORDER BY id;
 id |   nome    | ativo | limite 
----+-----------+-------+--------
  1 | Ana Maria | t     | 100.00
  2 | Bia       | f     | (null)
(2 rows)

-- joins, CTEs and subqueries
CREATE TABLE erp.pedidos (id int, cliente_id int, total numeric);
INSERT INTO erp.pedidos VALUES (10, 1, 50), (11, 1, 25), (12, 2, 10);
SELECT synonym.create('vendas.pedido', 'erp.pedidos');
 create 
--------
 
(1 row)

SELECT c.nome, sum(p.total) AS total
FROM vendas.cliente c JOIN vendas.pedido p ON p.cliente_id = c.id
GROUP BY c.nome ORDER BY c.nome;
   nome    | total 
-----------+-------
 Ana Maria |    75
 Bia       |    10
(2 rows)

-- the planner sees straight through the view: same plan as querying the table
EXPLAIN (COSTS OFF) SELECT * FROM vendas.cliente WHERE id = 1;
                 QUERY PLAN                 
--------------------------------------------
 Index Scan using clientes_pkey on clientes
   Index Cond: (id = 1)
(2 rows)

-- views and materialized views as targets
CREATE VIEW erp.ativos AS SELECT id, nome FROM erp.clientes WHERE ativo;
CREATE MATERIALIZED VIEW erp.resumo AS SELECT count(*) AS n FROM erp.clientes;
SELECT synonym.create('vendas.ativo', 'erp.ativos');
 create 
--------
 
(1 row)

SELECT synonym.create('vendas.resumo', 'erp.resumo');
 create 
--------
 
(1 row)

SELECT * FROM vendas.ativo ORDER BY id;
 id |   nome    
----+-----------
  1 | Ana Maria
(1 row)

SELECT * FROM vendas.resumo;
 n 
---
 2
(1 row)

-- partitioned table as target
CREATE TABLE erp.vendas (dia date, valor numeric) PARTITION BY RANGE (dia);
CREATE TABLE erp.vendas_2026 PARTITION OF erp.vendas FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
SELECT synonym.create('vendas.venda', 'erp.vendas');
 create 
--------
 
(1 row)

INSERT INTO vendas.venda VALUES ('2026-10-19', 99.90);
SELECT * FROM erp.vendas_2026;
    dia     | valor 
------------+-------
 10-19-2026 | 99.90
(1 row)

SELECT synonym, target, status FROM synonym.list ORDER BY synonym;
    synonym     |    target    | status 
----------------+--------------+--------
 vendas.ativo   | erp.ativos   | ok
 vendas.cliente | erp.clientes | ok
 vendas.pedido  | erp.pedidos  | ok
 vendas.resumo  | erp.resumo   | ok
 vendas.venda   | erp.vendas   | ok
(5 rows)

SELECT synonym.drop(s.synonym) FROM synonym.list s ORDER BY s.synonym;
 drop 
------
 
 
 
 
 
(5 rows)

DROP SCHEMA erp CASCADE;
NOTICE:  drop cascades to 5 other objects
DETAIL:  drop cascades to table erp.clientes
drop cascades to table erp.pedidos
drop cascades to view erp.ativos
drop cascades to materialized view erp.resumo
drop cascades to table erp.vendas
DROP SCHEMA vendas CASCADE;