CODEWITHSUNDEEP
postgresqlfunctionplpgsql
Aman Desai
Aman Desai

Reputation: 301

syntax error at or near ";" LINE 22 - Postgresql

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

Answers (1)

user330315
user330315

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

Related Questions