Snatch any course you want for just $15! Go to “Courses” and find a course ↓
Introduction
Side-effects in functions
Invoking functions

Instruction

Good job! As you probably remember, you can also use regular assignment with an SQL query, as in this example:

CREATE FUNCTION get_most_frequent_topic(OUT topic_id integer) AS $$
BEGIN
  topic_id := (
    SELECT p.topic_id
    FROM post AS p
    GROUP BY p.topic_id
    ORDER BY COUNT(1) DESC
    LIMIT 1
  );
END;
$$ LANGUAGE plpgsql;

Note that we don't declare the topic_id variable – it's already declared as the OUT parameter.

Exercise

Write a lock_post_author() function with two parameters:

  • An IN parameter named post_id.
  • An OUT parameter named locked_user_id.

The function should find the ID of the user who published the post with post_id and lock their account (i.e., set the is_locked column for this row to TRUE and locked_date to current_date).