Summer Deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Side-effects in functions
Invoking functions

Instruction

Great! PL/pgSQL has a special syntax for storing the result of a SELECT query in a variable. Take a look:

CREATE FUNCTION get_most_active_user(OUT user_id integer) AS $$
BEGIN
  SELECT u.id INTO user_id
  FROM user_account AS u
  JOIN post AS p
    ON u.id = p.user_id
  GROUP BY u.id
  ORDER BY COUNT(1) DESC
  LIMIT 1;
END;
$$ LANGUAGE plpgsql;

SELECT get_most_active_user();

We use the SELECT ... INTO variable_name syntax to store the query result in a variable. In this case, the query should return a single column and a single row. In theory, you can put the INTO variable_name expression almost anywhere in the query, but it's customary to put it right after the list of columns in SELECT.

Exercise

Write a function named get_most_frequent_topic() with one OUT parameter topic_id of type integer. The function should return the ID of the most frequent topic addressed on our forum.

Use the SELECT ... INTO syntax to store the ID of this topic in the topic_id parameter.

Stuck? Here's a hint!

Here's how the query should end:

GROUP BY 1
ORDER BY COUNT(*) DESC
LIMIT 1;