Postgresql流水帐(第六天):view
CREATE OR REPLACE view_name
AS
query
DROP VIEW [ IF EXISTS ] view_name;
一个复杂的 query:
SELECT cu.customer_id AS id,
(((cu.first_name)::text || ' '::text) || (cu.last_name)::text) AS name,
a.address,
a.postal_code AS "zip code",
a.phone,
city.city,
country.country,
CASE
WHEN cu.activebool THEN 'active'::text
ELSE ''::text
END AS notes,
cu.store_id AS sid
FROM (((customer cu
JOIN address a ON ((cu.address_id = a.address_id)))
JOIN city ON ((a.city_id = city.city_id)))
JOIN country ON ((city.country_id = country.country_id)));
将复杂的 query 存储为 view:
CREATE VIEW customer_master AS
SELECT cu.customer_id AS id,
(((cu.first_name)::text || ' '::text) || (cu.last_name)::text) AS name,
a.address,
a.postal_code AS "zip code",
a.phone,
city.city,
country.country,
CASE
WHEN cu.activebool THEN 'active'::text
ELSE ''::text
END AS notes,
cu.store_id AS sid
FROM (((customer cu
JOIN address a ON ((cu.address_id = a.address_id)))
JOIN city ON ((a.city_id = city.city_id)))
JOIN country ON ((city.country_id = country.country_id)));
A PostgreSQL view is updatable when it meets the following conditions:
- The defining query of the view must has exactly one entry in the FROM clause, which can be a table or another updatable view.
CREATE VIEW usa_cities AS SELECT
city,
country_id
FROM
city
WHERE
country_id = 103;
PostgreSQL Materialized Views
CREATE MATERIALIZED VIEW view_name
AS
query
WITH [NO] DATA;
REFRESH MATERIALIZED VIEW view_name;
REFRESH MATERIALIZED VIEW CONCURRENTLY view_name;
When you refresh data for a materialized view, PosgreSQL locks the entire table therefore you cannot query data against it. To avoid this, you can use the CONCURRENTLY option.
1 | REFRESH MATERIALIZED VIEW CONCURRENTLY view_name; |
With CONCURRENTLY option, PostgreSQL creates a temporary updated version of the materialized view, compares two versions, and performs INSERT and UPDATE only the differences. You can query against the materialized view while it is being updated. One requirement for usingCONCURRENTLY option is that the materialized view must have a UNIQUE index. Notice thatCONCURRENTLY option is only available from PosgreSQL 9.4.