Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Writing User-Defined Functions in PostgreSQL

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).