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

Great! The regular SELECT query does not work in a function body. Let's see an example:

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

SELECT get_most_active_user();

The body of this function simply runs a SELECT query. However, when you try to invoke the function you'll, get an error.

Exercise

Run the template query. Observe that PostgreSQL does not allow you to invoke a SELECT query without storing its value in a variable.