i52gWrTy

1-- Tabela Exemplo com BIT
2DISCARD ALL;
3
4BEGIN;
5
6DROP TABLE IF EXISTS foo CASCADE;
7CREATE TABLE foo (
8    id      BIGSERIAL PRIMARY KEY,
9    name    VARCHAR(100) NOT NULL,
10    deleted BIT
11);
12
13INSERT INTO foo (name, deleted) VALUES ('Foo 1', 0::bit), ('Foo 2', 1::bit);
14
15DROP SCHEMA IF EXISTS legacy CASCADE;
16CREATE SCHEMA legacy;
17CREATE VIEW legacy.foo AS SELECT id, name, deleted::int4 AS deleted FROM foo;
18
19CREATE OR REPLACE FUNCTION legacy.foo_insert() RETURNS TRIGGER AS
20$$
21BEGIN
22	IF NEW.id IS NULL THEN
23		INSERT INTO public.foo (name, deleted) VALUES (NEW.name, NEW.deleted::bit);
24	ELSE
25		INSERT INTO public.foo (id, name, deleted) VALUES (NEW.id, NEW.name, NEW.deleted::bit);
26	END IF;
27	RETURN NEW;
28END;
29$$
30LANGUAGE plpgsql;
31
32CREATE TRIGGER foo_insert INSTEAD OF INSERT ON legacy.foo FOR EACH ROW EXECUTE FUNCTION legacy.foo_insert();
33
34CREATE OR REPLACE FUNCTION legacy.foo_update() RETURNS TRIGGER AS
35$$
36BEGIN
37	UPDATE public.foo SET id = NEW.id, name = NEW.name, deleted = NEW.deleted::bit
38	 WHERE (id, name, deleted) = (OLD.id, OLD.name, OLD.deleted::bit);
39	RETURN NEW;
40END;
41$$
42LANGUAGE plpgsql;
43
44CREATE TRIGGER foo_UPDATE INSTEAD OF UPDATE ON legacy.foo FOR EACH ROW EXECUTE FUNCTION legacy.foo_update();
45
46COMMIT;
47
48
49-- ERROR
50SELECT * FROM foo WHERE deleted = 1;
51INSERT INTO foo (name, deleted) VALUES ('Foo 3', 0);
52UPDATE foo SET deleted = 1 WHERE deleted = 0;
53SELECT * FROM foo WHERE deleted = 1;
54
55
56-- SUCCESS
57SET search_path TO legacy, "$user", public;
58SELECT * FROM foo WHERE deleted = 1;
59INSERT INTO foo (name, deleted) VALUES ('Foo 3', 0);
60UPDATE foo SET deleted = 1 WHERE deleted = 0;
61SELECT * FROM foo WHERE deleted = 1;