# 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.
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:
|id INTEGER||name TEXT||email TEXT|
|1||Bob Smith||[email protected]|
|2||Rolf Smith||[email protected]|
|3||Susan Williams||[email protected]|
|4||Anne Pun||[email protected]|
|email_id INTEGER||user_id INTEGER||opened_time INTEGER|
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
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
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_rowis the name of the parameter and
email_opensis the type of the parameter. It is the same as the table name.
- Within the function,
email_open_rowis one row of the
email_openstable, so we can access
AS email_opened_agomakes sure that the returned table by the function has
email_opened_agoas the column name.
You might use it like this:
SELECT *, opened_ago(email_opens) FROM email_opens;
And it would give you this:
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;
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 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, that will give you more examples of functions.