Представлення (VIEW) в PostgreSQL
Синтаксис CREATE [OR REPLACE] VIEW view_name [(column_list)] AS select_statement CREATE VIEW v AS SELECT a.id, b.id FROM a,b; (1) CREATE VIEW v (a_id, b_id) AS SELECT a.id, b.id FROM a,b; (2) CREATE VIEW v AS SELECT a.id a_id, b.id b_id FROM a,b; (3) CREATE VIEW v AS SELECT group_concat (DISTINCT column_name (4) order BY column_name separator '+') FROM table_name;
Приклади модифікованих і немодифікованих представлень CREATE VIEW V_1 (date, count) AS SELECT date, COUNT (*) FROM table_1 GROUP BY date; CREATE VIEW V_2 AS SELECT * FROM Customers WHERE city = 'London'; CREATE VIEW V_3 (name, number, av) AS SELECT sname, snum, AVG (snum) FROM Salespeople WHERE city = ‘Kiev';
Приклади модифікованих і немодифікованих представлень CREATE VIEW V4 AS SELECT * FROM Salespeople WHERE snum (IN SELECT snum FROM Orders WHERE date = 10/01/2013); CREATE VIEW V5 AS SELECT snum, onum, cnum FROM Orders WHERE date IN (01/01/2013,02/02/2013);
SELECT * FROM oblik 18 запис(и/╕в) CREATE VIEW test_1 AS SELECT oblik.gazprovid_id, oblik.kilgaz, oblik.data_ FROM oblik WHERE oblik.gazprovid_id = 1 OR oblik.gazprovid_id = 2 GROUP BY oblik.gazprovid_id, oblik.kilgaz, oblik.data_; SELECT * FROM test_1 9 запис(и/╕в) Час виконання: 6.886 мсек SQL-запит виконано.
Модифікація представлень операція UPDATE Update test_1 Set kilgaz = 2000 where gazprovid_id = 2 Правило: create rule test_1_update as on update to test_1 do instead update oblik set kilgaz = new.kilgaz where gazprovid_id = new.gazprovid_id SELECT * FROM test_1
Модифікація представлень операція INSERT insert into test_1 values (3, 300, '2013-03-13') Правило: create rule test_1_insert as on insert to test_1 do instead insert into oblik values (new.gazprovid_id, new.kilgaz, new.data_) SELECT * FROM test_1
Модифікація представлень операція DELETE Delete from test_1 where gazprovid_id = 2 Правило: create rule test_1_delete as on delete to test_1 do instead delete from oblik where gazprovid_id = old.gazprovid_id SELECT * FROM test_1