-: Hi guys and welcome back.
In this video,
we're going to learn about composite types
as inputs to a function.
How to pass in entire tables and results sets to a function
so that there'll be operated on one by one.
This is really useful
because it starts to let us use our functions directly
on our data.
Let's take a look.
I've got some rather scary looking data in here
but don't worry.
This is just the next version on our email opens
that we saw in the last lecture.
We've got a user stable with an ID name and email.
We've got an email stable with an ID
and some email content that we're sending.
And finally, we've got the important table here
the email opens
which maps email ID to user ID as well as the opened time.
So every time a user opens one of our emails
we're gonna add a row to email opens.
We're gonna put in the email they opened,
the user that opened it,
and when they opened it.
Note that this is not how long ago they opened it,
but when they opened it as a timestamp.
We've got some primary key here,
email ID and user ID as a composite primary key
and then we've got the foreign keys email ID
going over to the email stable
and user ID going over to the users table.
Now let's look at the data we've got in here
are four users as before.
Now we've got three test emails
that they don't have a lot of content.
This could be much longer.
It could be HTML for example,
if you wanted to store your emails like that.
And finally, we've got our email opens
which has your some random data in here.
Email number three was opened by user number two
and this time email number two was opened
by user number two at that time and so on.
Like before, I'm gonna go ahead and hide these two
because we don't need to see them at all times.
We've got the data in there.
I've just written here a query
that joins email opens on user
so you can see who opened which email.
This is just really for your information but you can see
that every user has opened an email here
and we've got this open time that we can't really parse.
So now what we're gonna do is we're gonna write a function
that given a row in the email opens table.
So these three columns here
is gonna give us how long ago that was in seconds, okay?
And the function is gonna accept an entire row of data,
not just one of the columns.
So how do we do that?
Well, I'm gonna go ahead and create a new thing down here.
I'm gonna delete this one.
I'm gonna write my function in here at the top.
And then we're going to select down here to see the output.
So we're gonna start with create or replace function.
I'm gonna call it opened ago.
So we can call it and we know
that this tells us how long ago something was opened.
And now here comes the interesting part.
We're gonna first of all as we did when we used
normal data types or built-in data types
we're gonna give the parameter a name.
I'm gonna call it email open row
but the type of the parameter has to be the table name
for which this row is coming from.
So that's gonna be email on the score opens.
That's what our table is called.
Then is gonna return an integer.
So we do returns integer
and then as usual,
our double dollar language SQL.
What do we put in here?
You have to know that there is a way
to get the current timestamp
as per the PostgreSQL timezone settings
using select current timestamp.
So assuming that the PostgreSQL timezone settings
match the timezone of these timestamps
which is probably gonna be UTC
if you'll be following what I'm suggesting
throughout this course.
Then these two are gonna be apples to apples
as opposed to apples to oranges.
So we're gonna select the current timestamp
and then what we're gonna do is gonna subtract from it
the email_open_row.opened_time
and that's gonna give us the opened time column value
from the email open row
that is currently being processed in this function.
You can also give it an as there
so that it looks a bit better,
email opened ago.
Like that if I can type.
Now, there's a small problem with this
and that is that current timestamp
is essentially Postgres representation
of a datetime object in Python.
So we can't quite subtract
from current timestamp an integer.
If we run this you'll see that you get a
type mismatch there.
No operator matches the given name and blah, blah, blah.
You can't do timestamp with timezone minus integer.
It doesn't work.
So we have to get the timestamp as an integer from here.
How we do that is we use the extract function
and we say epoch from current timestamp.
And that gonna give us the number
that represents this timestamp.
If we run this we get that we actually
are working with a double
as opposed to an integer in here so we can't
return it because whenever you subtract
an integer from a double,
you return a double.
This works if we return a double precision.
For example like this,
but we do want to return an integer.
We're now interested in the double-precision
of the timestamp,
the microseconds, and all that.
So we can use the cast function to cast this to an integer.
So a quick primer on cast.
We can do as integer.
Make sure to close the brackets there.
And now what we're doing is we're getting the epoch
from the current timestamp
and we're casting it as an integer
and then we're subtracting it from here
and we can finally return integer.
So now we can run this and that's gonna give us
how long ago the email was opened.
So now let's create another element down here
and we can do select star
and opened ago of email opens from email opens.
That is going to pass in each row
to this function as it being processed
and we're gonna select everything else as well.
Pressing run in here gives us the row
as well as how long ago this is.
This number here.
I mean, it's 17 million seconds.
I'm not exactly sure how long ago this was.
So we can actually use this a bit better.
So let's go ahead and write this differently.
We're gonna do select users.
Don't start from users.
We're gonna join on email opens on users though ID
is equal to email_opens.user_id.
And this is gonna give us user information
and when they opened anything.
Well, just the user information
because we haven't selected everything else.
But now we can do where opened ago.
All the email opens is less than let's say 604,800.
Do need to move the semi-colon to the right place though.
Nobody is here so let's say 20 million.
That's gonna be everybody.
And you can see here how if we select star,
so you can see that here we're getting everybody
but we can pick a better number like this one
and now we only get two people
that opened this less than this number of seconds ago.
Remember that as well as using the function down here,
you can also select it here.
Opened ago on email opens.
And that is now gonna give you that information as well
in case you're interested in that.
There's a lot more to learn on SQL functions
like output parameters or tables as return values,
variable number of arguments,
and a lot more.
I recommend reading through the official documentation.
If you have the time it's worth reading through it
but I've also linked another tutorial down
in the resources section of this lecture
that has a little bit more information.
A couple of different explanations as well
in case you want to check that out.
All right, thank you guys for joining me in this video.
Thank you for watching and I'll see you in the next one.