Reputation: 301
I am creating a user defined function in PostgreSQL and facing error.
Also, please suggest a better way (if any) to execute this function.
CREATE FUNCTION public.mark_enrollment_completed(IN enrollment_id text)
RETURNS boolean
LANGUAGE plpgsql
AS $BODY$
DECLARE
total_contents INTEGER;
completed_content_count INTEGER;
user_id TEXT;
course_id TEXT;
was_marked BOOLEAN;
BEGIN
SELECT user_id, course_id INTO user_id, course_id FROM enrollments WHERE enrollment_id = enrollment_id;
SELECT count(*) INTO total_contents FROM course_contents WHERE course_id = course_id;
SELECT count(*) INTO completed_content_count FROM completed_contents WHERE user_id = user_id;
IF total_contents = completed_content_count THEN
UPDATE enrollments SET is_completed = true WHERE enrollment_id = enrollment_id;
SET was_marked = true;
ELSE
SET was_marked = false;
RETURN was_marked;
END;
$BODY$;
Error:
ERROR: syntax error at or near ";"
LINE 22: END;
^
SQL state: 42601
Character: 750
Upvotes: 0
Views: 1433
Reputation:
As documented in the manual assignment is done using :=
operator - there is no SET in PL/pgSQL.
You are also missing the END IF
IF total_contents = completed_content_count THEN
UPDATE enrollments SET is_completed = true WHERE enrollment_id = enrollment_id;
was_marked := true;
ELSE
was_marked := false;
END IF;
RETURN was_marked;
Upvotes: 1