目标是根据值1和0将我的活动列转换为yes或no。此外，使用触发器自动更新。当我更新表时，它不会在新表中显示yes或no值。

CREATE TABLE Detailed_customers(
customer_id integer,
first_name varchar(20),
last_name varchar(20),
active varchar(3),
rental_date timestamp,
return_date timestamp,
last_update timestamp);

CREATE TABLE Summary_customers(
customer_id int,
active varchar(3),
rental_date timestamp,
return_date timestamp);

C.

SELECT customer.customer_id, customer.first_name, customer.last_name, customer.active, rental.rental_date, rental.return_date, rental.last_update
FROM customer
JOIN rental on customer.customer_id = rental.customer_id;

D.

SELECT first_name, last_name, active, rental_date, return_date, store.store_id
FROM customer
INNER JOIN rental
ON customer.customer_id = rental.customer_id
INNER JOIN store
ON store.store_id = customer.store_id
WHERE active = 1
group by customer.first_name, customer.last_name, customer.active, rental.rental_date, rental.return_date, store.store_id
order by store.store_id asc;

E.

CREATE TRIGGER Detailed_update
AFTER UPDATE
ON Detailed_customers
FOR EACH STATEMENT
EXECUTE PROCEDURE active_status();

F.

CREATE OR REPLACE PROCEDURE refreshed_data()
LANGUAGE PLPGSQL
AS
$$
BEGIN
DELETE FROM Detailed_customers;
DELETE FROM Summary_customers;

INSERT INTO Detailed_customers
SELECT customer.customer_id, first_name, last_name, active, rental_date, return_date, store.store_id
FROM customer
INNER JOIN rental
ON customer.customer_id = rental.customer_id
INNER JOIN store
ON store.store_id = customer.store_id
WHERE active = 1, store_id = 1
group by customer.customer_id, customer.first_name, customer.last_name, customer.active, rental.rental_date, rental.return_date, store.store_id
order by customer.first_name asc;

INSERT INTO Summary_customers
SELECT customer.customer_id, active, rental.rental_date, rental.rental_date
FROM customer
INNER JOIN rental
ON customer.customer_id = rental.customer_id
INNER JOIN store
ON store.store_id = customer.store_id
WHERE active = 1, store_id=1
group by customer.customer_id, customer.active, rental.rental_date, rental.return_date;

END;
$$;

CALL refreshed_data();
SELECT * FROM Summary_customers;
SELECT * FROM Detailed_customers;