Postgresql流水帳(第六天):view

標籤：

CREATE OR REPLACE view_name

AS

query

DROPVIEW[IFEXISTS]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 (((customercu

???? JOIN addressa 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 (((customercu

???? JOIN addressa 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.
• The defining query must not contain one of the following clauses at top level:?GROUP BY,HAVING,?LIMIT, OFFSET,?DISTINCT, WITH,?UNION, INTERSECT, and EXCEPT.
• The selection list must not contain any window function or?set-returning function?or any aggregate function such as?SUM,?COUNT,?AVG,?MIN,?MAX, etc.

?

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;

?

REFRESHMATERIALIZEDVIEWview_name;

REFRESHMATERIALIZEDVIEWCONCURRENTLYview_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.

Postgresql流水帳(第六天):view