PostgreSQL: طرق العرض وطرق العرض المادية في PostgreSQL

CREATE [OR REPLACE] VIEW view_name [(column_aliases)] AS
  SELECT ...;

CREATE VIEW v_daily_sales AS
SELECT
  order_date,
  region,
  COUNT(*)               AS order_count,
  SUM(amount)            AS total_revenue,
  AVG(amount)            AS avg_order_value
FROM orders
GROUP BY order_date, region;

 count 
-------
     5
(1 row)

SELECT * FROM v_daily_sales
WHERE order_date >= '2025-01-01'
ORDER BY total_revenue DESC;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

ALTER VIEW v_daily_sales RENAME TO v_daily_summary;

DROP VIEW IF EXISTS v_daily_summary CASCADE;

-- SQL statement executed successfully

CREATE VIEW v_active_users AS
SELECT user_id, name, email, status
FROM users
WHERE status = 'active';

CREATE TABLE

UPDATE v_active_users SET name = 'Alice Wang' WHERE user_id = 1;

DELETE FROM v_active_users WHERE user_id = 99;

INSERT INTO v_active_users (user_id, name, email, status)
VALUES (101, 'Charlie', 'charlie@example.com', 'active');

CREATE VIEW v_active_users_strict AS
SELECT user_id, name, email, status
FROM users
WHERE status = 'active'
WITH CHECK OPTION;

UPDATE v_active_users_strict SET status = 'inactive' WHERE user_id = 1;

ERROR: new row violates check option for view "v_active_users_strict"
DETAIL: Failing row contains (1, ..., inactive).

INSERT INTO v_active_users (user_id, name, email, status)
VALUES (200, 'Bob', 'bob@example.com', 'active');

SELECT * FROM v_active_users WHERE user_id = 200;

 user_id | name |       email        | status
---------+------+--------------------+--------
     200 | Bob  | bob@example.com    | active

CREATE MATERIALIZED VIEW mv_monthly_sales AS
SELECT
  DATE_TRUNC('month', order_date)::date AS month,
  region,
  COUNT(*)    AS order_count,
  SUM(amount) AS total_revenue,
  AVG(amount) AS avg_order_value
FROM orders
GROUP BY DATE_TRUNC('month', order_date), region
WITH DATA;

 count 
-------
     5
(1 row)

SELECT * FROM mv_monthly_sales
WHERE month >= '2025-01-01'
ORDER BY total_revenue DESC;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

REFRESH MATERIALIZED VIEW mv_monthly_sales;

-- SQL statement executed successfully

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales;

-- SQL statement executed successfully

CREATE UNIQUE INDEX idx_mv_monthly_sales_pk
  ON mv_monthly_sales (month, region);

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales;

CREATE MATERIALIZED VIEW mv_expensive_report AS
SELECT ... FROM ... WITH NO DATA;

REFRESH MATERIALIZED VIEW mv_expensive_report;

CREATE TABLE

SELECT cron.schedule(
  'refresh_monthly_sales',
  '0 4 * * *',
  $$REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales$$
);

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

CREATE VIEW v_user_public AS
SELECT user_id, name
FROM users;

GRANT SELECT ON v_user_public TO reporter_role;

REVOKE SELECT ON users FROM reporter_role;

CREATE TABLE

CREATE MATERIALIZED VIEW mv_sales_report AS
SELECT
  DATE_TRUNC('month', o.order_date)::date AS month,
  p.category,
  o.region,
  COUNT(*)                                AS order_count,
  COUNT(DISTINCT o.customer_id)           AS unique_customers,
  SUM(o.amount)                           AS total_revenue,
  SUM(o.amount) FILTER (WHERE o.amount >= 50000) AS big_deal_revenue,
  AVG(o.amount)                           AS avg_order_value,
  SUM(oi.quantity * oi.unit_price)        AS total_gmv
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY DATE_TRUNC('month', o.order_date), p.category, o.region
WITH DATA;

CREATE UNIQUE INDEX idx_mv_sales_report_pk
  ON mv_sales_report (month, category, region);

CREATE VIEW v_sales_dashboard AS
SELECT
  month,
  region,
  SUM(total_revenue)  AS region_revenue,
  SUM(order_count)    AS region_orders,
  SUM(unique_customers) AS region_customers
FROM mv_sales_report
GROUP BY month, region;

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_sales_report;