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

Returning tables and sets
Summary

Instruction

Cool! There is also another way in Postgres to declare a function as one that returns a table: using the syntax RETURNS SETOF. If a function is declared as returning SETOF, then each row it outputs is returned as part of the result set.

The example below shows how we can retrieve a set of employees using the RETURNS SETOF statement along with the return type (employee, in this case):

CREATE FUNCTION get_employees()
RETURNS SETOF employee
AS $$
BEGIN
  RETURN QUERY
    SELECT *
    FROM employee
    WHERE end_date IS NULL;
END;
$$ LANGUAGE plpgsql;

This syntax is used instead of the RETURNS TABLE syntax when you don't want to repeat the definition of an existing table. Instead, you can just give the name of the table (here, employee) and the function will return a set of employee rows.

Exercise

Create a new function named get_users_with_no_posts() that returns SETOF user_account (users) for users who have no posts in the post table. The function should take no arguments.