PostgreSQL Flow Account (sixth day): view

Source: Internet
Author: User
Tags postgresql

CREATE OR REPLACE view_name

As

Query

DROP VIEW[IFEXISTS]view_name;

a complex query:

SELECT cu.customer_id as ID,

? ??? text | | " : text | | (cu.last_name):: Span style= "Font-family:times New Roman" >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 Span style= "Font-family:consolas" > ((a.city_id = city.city_id))

???? JOIN Country on (city.country_id=country.country_ (ID) ));

?

To store a complex query as a view:

CREATE VIEW Customer_master as

SE Lect cu.customer_id As id,

? ??? text | | " : text | | (cu.last_name):: Span style= "Font-family:times New Roman" >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 Span style= "Font-family:consolas" > ((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 have 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:? GROUPby,have,? 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, huh? 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 ;

?

REFRESH materializedVIEWview_name;

REFRESH materializedVIEWconcurrentlyview_name;

When You Refresh data for a materialized view, posgresql locks the entire table therefore you cannot query data against it. To avoid this, can I 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 the versions, and performs? INSERT? and? UPDATEonly the differences. You can query against the materialized view and it is being updated. One requirement for usingconcurrently? option is, the materialized view must have a?UNIQUE? index. Notice thatconcurrentlyoption is available from Posgresql 9.4.

PostgreSQL Flow Account (sixth day): view

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.