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.
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.
Here's how the query should end:
GROUP BY 1 ORDER BY COUNT(*) DESC LIMIT 1;
