It's Autumn! Prices fall like leaves! - hours only!Up to 80% off on all courses and bundles.-Close
Returning tables and sets
Summary

Instruction

Well done! Let's start with a practical example. Below is a function that returns a table of all events from a given range of dates:

CREATE FUNCTION get_events (date_from date, date_to date)
RETURNS TABLE (
  event_name varchar,
  city varchar,
  event_date date
) AS $$
BEGIN
  RETURN QUERY
    SELECT e.name, e.city, e.event_date
    FROM event AS e
    WHERE event_date BETWEEN date_from AND date_to;
END;
$$ LANGUAGE plpgsql;

The function above accepts two parametersdate_from and date_to – that are used to retrieve the relevant events from the event table.


The RETURNS TABLE syntax, along with definitions of the columns, informs Postgres that this function should return a table.
Observe that you must specify the column names and column data types of the result table:

RETURNS TABLE (
  event_name varchar,
  city varchar,
  event_date date
)

In the function body, we use the RETURN QUERY syntax followed by the SELECT statement. The types and number of columns specified in the SELECT statement must match those of the columns we want to return. The result of the SELECT query is the result of the function.

  RETURN QUERY
    SELECT e.name, e.city, e.event_date
    FROM event AS e
    WHERE event_date BETWEEN date_from AND date_to;

Note that we introduced the e alias for the event table. We've done that to prevent ambiguity – Postgres would throw an error because we've named columns of the returned function indentically.

Exercise

Create a function named get_active_users() that returns a table with the following columns: user_id int and user_name varchar.

This function has no parameters, and the SQL statement you implement should return users (from the user_account table) who have not been deleted (is_deleted = FALSE) and whose accounts are not currently locked (is_locked = FALSE). The result set has to be ordered by user name (the name column).