test_create.sql

CREATE TABLE address (
	id		    int,
	street	    varchar(40),
	no		    varchar(10),
	city	    varchar(40),
	zipcode		varchar(10),

	PRIMARY KEY (id)
);


INSERT INTO address VALUES 
    (1, 'Dejvicka', '189/5', 'Praha 6', '16059'),
    (2, 'Masarykovo namesti', '24/13', 'Ostrava - Moravska Ostrava', '70200'),
    (3, 'Novinarska', '3178/6a', 'Ostrava', '70200'),
    (4, 'Orlicka', '4/2020', 'Praha 3', '13000'),
    (5, 'Dr. Maye', '686/5', 'Ostrava - Marianske Hory', '70900'),
    (6, 'Technicka', '2/19', 'Praha 6', '16627'),
    (7, 'Trebohosticka', ' 987/5', 'Praha 10', '10000'),
    (8, 'Tr.V.Klementa', '869', 'Mlada Boleslav', '29360'),
    (9, 'Libalova', '1/2348', 'Praha 4 - Chodov', '14900'),
    (10, 'Evropska', '33a', 'Praha 6', '16000'),
    (11, 'Daimlerova', '1161/6', 'Plzen - Skvrnany', '30100'),
    (12, 'Peroutkova', '5', 'Brno', '60200'),
    (13, 'Hilleho', '4', 'Brno', '60200'),
    (14, 'Spalena', '75/16', 'Praha 1', '11306'),
    (15, 'Vanickova', '7', 'Praha 6', '12969'),
    (16, 'Klimentska', '46/2', 'Praha', '11002'),
    (17, 'Anglicka', '26', 'Praha 2', '12000');

CREATE MATERIALIZED VIEW address_mv AS
SELECT 	id, 
		city, 
		zipcode 
FROM 	address
WHERE   city LIKE 'Praha%';

ALTER MATERIALIZED VIEW address_mv REFRESH;

SELECT * FROM address_mv;

--
-- CREATE INDEX
--
CREATE INDEX address_mv_city_idx ON address_mv(city);

SET enable_seqscan TO off;
EXPLAIN SELECT zipcode FROM address_mv WHERE city = 'Praha 6';
SET enable_seqscan TO on;

--
-- COMMENT
--
COMMENT ON MATERIALIZED VIEW address_mv IS '... stuff ...';
COMMENT ON COLUMN address_mv.zipcode IS 'that 5-digit number';

--
-- SCHEMA
--
CREATE SCHEMA myschema;
ALTER MATERIALIZED VIEW address_mv SET SCHEMA myschema;
ALTER TABLE myschema.address_mv SET SCHEMA public;

DROP SCHEMA myschema;
--
-- RENAME
--
ALTER MATERIALIZED VIEW address_mv RENAME TO address_mw;
ALTER TABLE address_mw RENAME TO address_mv;

--
-- OWNER
--
CREATE USER myuser SUPERUSER;
ALTER MATERIALIZED VIEW address_mv OWNER TO myuser;
ALTER TABLE address_mv OWNER TO postgres;

DROP USER myuser;
--
-- TABLESPACE
--
CREATE TABLESPACE mytblspc LOCATION '/usr/local/pgsql/test/DATA/pg_mytblspc';
ALTER MATERIALIZED VIEW address_mv SET TABLESPACE mytblspc;
ALTER TABLE address_mv SET TABLESPACE pg_default;

DROP TABLESPACE mytblspc;
--
--
--
SELECT * FROM pg_views WHERE viewname = 'address_mv';
SELECT pg_get_viewdef(oid) FROM pg_class WHERE relname = 'address_mv';
SELECT pg_get_ruledef(oid) FROM pg_class WHERE relname = 'address_mv';
SELECT pg_relation_filepath(oid) FROM pg_class WHERE relname = 'address_mv';
SELECT pg_relation_filenode(oid) FROM pg_class WHERE relname = 'address_mv';
SELECT pg_table_size(oid) FROM pg_class WHERE relname = 'address_mv';