# Composite types and sets in functions

In PostgreSQL terms, a "set" is a collection of rows. A "composite type" is any non-base type, such as a table.

Functions can accept tables as arguments, and they can also return them. Here's an example^[1].

Let's say we've upgraded our tables in the last example so now we have a users table and a separate email_opens table. Please assume there's a separate emails table that contains information about the e-mails we have sent:

users

id INTEGER	name TEXT	email TEXT
1	Bob Smith	bobsmith@example.com
2	Rolf Smith	rolfsmith@example.com
3	Susan Williams	susanwilliams@example.com
4	Anne Pun	annepun@example.com

email_opens

email_id INTEGER	user_id INTEGER	opened_time INTEGER
3	2	1572393600
2	2	1572220800
1	3	1572393600
2	3	1572480000
1	4	1572480000
1	1	1572393600

TIP

The email_opens table has these constraints:

PRIMARY KEY (email_id, user_id)
FOREIGN KEY (email_id) REFERENCES email (id)
FOREIGN KEY (user_id) REFERENCES users (id)

As you can see, Bob Smith has never opened an e-mail according to our email_opens table. However, Rolf Smith has opened e-mails 2 and 3.

Let's write a function that, given a row from the email_opens table, tells us how long ago that was, in seconds. Note that the function should accept the entire row of data for simplicity.

Here's the function in Python:

from datetime import datetime

def opened_ago(user_data):
    return datetime.now().timestamp() - user_data[2]

And here it is, in SQL:

CREATE OR REPLACE FUNCTION opened_ago(email_open_row email_opens) RETURNS INTEGER AS $$
    SELECT CAST(EXTRACT(epoch FROM CURRENT_TIMESTAMP) AS INTEGER) - email_open_row.opened_time AS email_opened_ago;
$$ LANGUAGE SQL;

A few important things about this:

The type of the parameter must be the same as the table name, so in email_open_row email_opens, email_open_row is the name of the parameter and email_opens is the type of the parameter. It is the same as the table name.
Within the function, email_open_row is one row of the email_opens table, so we can access .opened_time.
AS email_opened_ago makes sure that the returned table by the function has email_opened_ago as the column name.

You might use it like this:

SELECT *, opened_ago(email_opens)
FROM email_opens;

And it would give you this:

email_id	user_id	opened_time	opened_ago
3	2	1572393600	10865985
2	2	1572220800	11038785
1	3	1572393600	10865985
2	3	1572480000	10779585
1	4	1572480000	10779585
1	1	1572393600	10865985

Here's a more interesting use case. Let's say we want to get users that opened an e-mail less than a week ago:

SELECT users.* FROM users
JOIN email_opens ON users.id = email_opens.user_id
WHERE opened_ago(email_opens) < 604800;

TIP

A question you might have is: why define a function at all? Why not just create a view like this one, without the function?

SELECT users.* FROM users
JOIN email_opens ON users.id = email_opens.user_id
WHERE (
    CAST(EXTRACT(epoch FROM CURRENT_TIMESTAMP) AS INTEGER) - email_opens.opened_time
    ) < 604800;

You should think of creating a view as giving you a rather static result. You gain a window that lets you see the data the view calculates. But a function gives you the flexibility to perform the calculation whenever you need to.

If you only ever wanted to get users who opened an e-mail 7 days ago or less, creating the view on its own and foregoing the function is a good idea.

But if you potentially want to display when an e-mail was last opened, or maybe you want to create views for 7 days and 30 days, then having the function will save you code duplication.

# Wrapping Up

There's a lot more to learn on SQL functions, such as:

Output parameters
Tables as return values
Variable numbers of arguments

I wholeheartedly recommend reading through the official documentation^[2] and getting at least familiar with the sort of things you can do. One day it'll come in handy, and you'll know where to find the information you need! There are also other tutorials that you can read^[3], that will give you more examples of functions.

← User-defined functions Stored procedures vs functions →