CODEWITHSUNDEEP
postgresql
Dakota Tegan
Dakota Tegan

Reputation: 13

Syntax Error on CREATE TRIGGER and CREATE PROCEDURE

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

Upvotes: 1

Views: 45

Answers (1)

Evgeniy Chekan
Evgeniy Chekan

Reputation: 2665

You need ; after END; $$ to separate create function from create trigger

Upvotes: 1

Related Questions