I am trying to create a FUNCTION, TRIGGER, and PROCEDURE. Each time I try to run it, I get a Syntax Error on/around the CREATE line. Below is what I have:
------------------------------------------
CREATE FUNCTION summary_refresh_fun()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
DELETE FROM summary;
INSERT INTO summary (
SELECT
concat_ws (', ', last_name, first_name) AS customer_name,
email,
COUNT(customer_id)
FROM detailed
GROUP BY customer_id, customer_name, email
-- HAVING count(customer_id) > 30
ORDER BY count(customer_id)DESC
LIMIT 100
);
RETURN NEW;
END; $$
----------------------------------------------
CREATE TRIGGER summary_refresh
AFTER INSERT ON detailed
FOR EACH STATEMENT
EXECUTE PROCEDURE summary_refresh_fun();
---------------------------------------------
CREATE PROCEDURE refresh_tables()
LANGUAGE plpgsql
AS $$
BEGIN
DELETE FROM detailed;
INSERT INTO detailed(
customer_id, -- customer
first_name, -- customer
last_name, -- customer
email, -- customer
rental_id, -- rental
rental_date, -- rental
return_date, -- rental
staff_id --rental
)
SELECT
c.customer_id, c.first_name, c.last_name, c.email,
r.rental_id, r.rental_date, r.return_date, r.staff_id
FROM rental AS r
INNER JOIN customer AS c ON c.customer_id = r.customer_id;
END;$$I am getting errors at CREATE TRIGGER and CREATE PROCEDURE:
[Syntax Error on CREATE TRIGGER][1]
[Syntax Error on CREATE PROCEDURE][2]
Any idea on why this Syntax Error is occurring? Using PostgreSQL 13 [1]: https://i.sstatic.net/TIyPP.png [2]: https://i.sstatic.net/gL5ya.png
