-: Hi guys and welcome back.
In this video we're gonna learn about
User-defined functions.
We've been using the built-in functions Postgres
for a while, but we can actually define our own,
and they can take arguments and they can return values
and all sorts of stuff.
So they're really useful.
Let's take a look.
What we've got here is a sample data set,
I'm opened here in db<>fiddle
and this is in the resources section of this lecture
as well in case you want to open it as well.
What I've got here, is I'm creating a table
of name, email and last opened.
Then I'm entering four rows into it.
Bob Smith last opened one of the emails we sent him,
65,000 seconds ago, that's what that column stands for.
Rolf Smith last opened an email 8 million seconds ago.
Susan 56 seconds ago and then Anne.
At the bottom here, I've got a SELECT *,
which has given me the data
that we've got at this point in time.
Now what I'm gonna do is, I'm gonna create a new entry here,
so that we can create and run our functions,
and I'm going hide these in here.
So those are just hidden, they're still gonna run.
They're just not showing up there,
so that we can focus on the function.
A function in Postgres is a reusable piece of code
that executes when called.
It can accept arguments and return output,
very similar to Python functions.
They were originally introduced in Postgres 9.5.
Right now we're in version 12 as you can see.
And but you can use a variety of programming languages
to write them in and functions can do basically anything
you can do in Postgres except, handle transactions.
So you can't commit, or rollback in functions.
That's what stored procedures are for,
but you can use them for everything else,
including calculating values
and returning them and so forth.
When you write functions using SQL,
you can still update and insert data,
but you can't group them into a transaction.
Okay.
And so more information is in the e-book,
if you want, you know, a detailed explanation
on everything that transactions do and how they do it.
And we've also got references in the resources section
of this lecture, as well as at the bottom of the e-book.
So let's take a look at how we can write our own function.
Let's say that we've got this data here
and we want to prune our users.
This is something that's very commonly done
with mailing lists, when a user doesn't respond
to your emails in a long time you delete them
so that you don't have to pay for your subscription
for that user.
So let's say that we want to do something like
DELETE-FROM users WHERE last_opened > 604800.
So, a week of seconds.
You could just do this and, you know, that's fair enough.
But this doesn't really mean much.
It just means that, you know, you are deleting from users,
where last open is greater than 600,000 seconds
but you may not even know what that means.
And so, instead we can call this a name,
just as we would use Python,
we can give our statements, a name.
We can do the same in SQL.
So what we're gonna do is I'm gonna cut this here,
and we're gonna do CREATE-FUNCTION
and I'm gonna call it delete_inactive.
And then we put the brackets at the end, like that.
Now we specify the return type of the function,
just as we do in Python, with type painting,
but here it is compulsory
so we can see create function delete_inactive.
And we're gonna see returns void.
If you are familiar with,
basically any programming language except Python,
you'll know that void means returns nothing.
So that's what we're doing there.
Then we'll say AS, and now we're gonna start
defining what the function body is,
but the function body must be enclosed in something
that tells Postgres that this is the function body.
So that is the double dollar sign.
So, between the double dollar sign,
we're gonna put our function body,
ie, the code that's gonna run when we execute the function,
but at the end there's something very important.
We have to tell Postgres what language
we're writing the function in.
Because you can write it in SQL,
as we have been learning through this course,
but there's also a bunch of other languages
we can use like plpgSQL and others.
So here we're gonna say language, SQL.
So we're gonna write this function in SQL,
and now Postgres knows that.
What we're gonna put in here, is the DELETE FROM users
where last open greater than 600,000.
Okay, so if we run this right now
that's gonna create the function,
just as we do in Python when we define a function,
but it's not gonna run it.
So we do have to do something else
in order to run the function, and that is, Select.
Select is used for basically everything in SQL.
And this makes a little bit less sense
than it would normally
but we're gonna select delete_inactive.
And that is how we run functions.
So if we run this,
then you'll see that our data has changed.
We've deleted everybody that didn't open an email in a week.
We no longer interested in them.
So that's what happens.
Note the return value here of this table is void or null,
but you still do get a column back with that.
This is, you know, cool and it can be useful,
but functions really become useful
when we can pass in arguments, we can say, delete_inactive,
where they've been inactive for longer than a week
or longer than a month, let's say.
So we can prune users at different points in time.
So here we're gonna pass in or add a parameter.
Here is the parameter name and the data type.
And then the function remains exactly the same,
but now we have access to this,
as if it were a variable essentially.
So we'll do greater than seconds.
And now, when we select the delete_inactive,
we can pass in the number of seconds
that they must have been inactive for.
So just so the data down here changes,
I'm gonna select delete_inactive of,
let's say, 50,000 seconds.
And now, Susan Williams should be the only user left
after running this function, so I'll run it.
And you can see that that is what happens.
Something important, though,
when you are creating multiple functions.
Here we're only creating one on this data set,
every time we run this fiddle,
it essentially deletes everything
and recreates everything from scratch.
That's why we keep the CREATE TABLE and insert rows above.
But if you are creating functions in your database,
do remember that creating a function, you know,
it doesn't go away on its own.
So you can always delete functions if you need to.
To delete a function, you simply do, DROP FUNCTION
and then the name.
So here we're gonna say delete_inactive.
But notice that delete_inactive
is not the full name of our function.
It also needs, the data types for its parameters.
So we'll say NUMERIC in there.
Let's say that we create delete_inactive
without a parameter, and also delete_inactive
with a parameter that is okay.
In SQL, you can do that, and you can have two functions
with the same name, but different parameters.
So when you're dropping functions,
that's why you need the parameters as well.
Let's take a look at how we can return values
from a function.
What we're gonna do is,
we're gonna keep this delete function,
just as it is right now,
but we're going to make it give us the count of users
that were deleted or how many users were deleted.
The first thing we have to do is,
we have to match the return type of the function
to the return type of the count function.
Because we're gonna use the count function
in order to calculate how many users were deleted.
Count RETURNS BEGINT.
So we're gonna make our function RETURN BEGINT as well.
Then we're gonna do is, we're gonna create a subquery
for this delete statement.
So we'll do WITH deleted AS this.
And that's gonna give us a table
that represents the result of running this.
We do have to add here a RETURNING * to make sure
that the delete statement returns what it deleted.
We've learned about the returning keyword,
you can usually with delete as well, to give you that.
Now I'm gonna remove the semi colon,
go back down to the next line,
and we're gonna do SELECT COUNT(*) FROM deleted.
Functions are gonna return the value of the last query
that evaluated, so here that's the Select count.
So that's what this function is gonna return.
We can run this to make sure it works, seems to like it.
And now I'm gonna add a new thing down here
so that we can run this function.
So we'll do select Delete_inactive.
Let's say, it is 86400 for a day.
And we're gonna run this.
And you can see that we get two back,
because we deleted two users.
If you're creating a function and it already exists
because you've got the function name
and the parameter already be used by another function,
you can say create or replace function,
and that is gonna not give you an error
if the function already exists.
There are more types of parameters and return values
for functions including, you know,
whole tables and results sets that you can use.
So we've got a bunch of references
in the resources section of this lecture.
And we're also gonna look at a bit more
in the next lecture as well.
So thank you guys for joining me in this video.
Thanks for watching, and I'll see you in the next one.