Jose: Hi guys, and welcome back.
In this video, we're going to learn
about built-in functions in PostgreSQL
and how we can use them to get a random voter,
the voter for one of our auctions.
So first of all, what are functions?
Very much like Python functions,
names for blocks of code that we can run.
And just like Python functions,
they can have arguments and parameters as well.
There's a lot of built-in functions,
including mathematical and aggregate functions.
Here's just two examples.
Here's a mathematical function.
Select random, and then put the brackets at the end
and that tells PostgreSQL that you wanna run this function.
That's gonna give you a random number between zero and one.
And you've got something, for example,
select count and then put id, the id column,
in the brackets from polls.
That's gonna give you the number of values
in the id column from the polls data sect.
So, mathematical functions.
We've got a few examples here.
random() gives you a value between 0.0 and 1.0.
So, floating point value.
abs(x) accepts an argument
and gives you it's absolute value.
So for example, minus five would be converted to five.
mod(y,x) gives you the remainder of Y and X,
kind of like the Python modulus operator.
And there's loads more, dozens more.
So I've left a link to the documentation
over in the resources section
in case you wanna learn more
about these mathematical functions.
Really interesting are aggregate functions, though.
We've got something like count(expr).
And count(expr) doesn't take just a normal argument,
it takes an expression.
And it gives you the number of rows
resulting from evaluating the expression.
So for example, if you give it a column inside a select,
it's gonna calculate how many values are in that column
out of the entire result set.
avg(expr), for example, gives you the average value
of those rows.
max(expr) gives you the maximum value of the rows.
And again, there's loads more.
And again, I've also left a link to the documentation
in the resources section.
I'd encourage you to at least know
some of the aggregate functions,
but really, if you can read through the documentation,
you're then gonna know more or less what's there.
And then later on, it may come to you
when you're trying to write a query
and you're not quite sure how to do it, you may remember,
I remember looking at the documentation
and there was a function that maybe could help here.
That's really the aim of reading the documentation
at this point.
So how are we gonna get a random voter?
Well, we're gonna use the random function.
Select '*' from votes.
It's gonna give us all the votes.
Where option id equal value,
is gonna give us all the votes for a particular option.
Order by random is gonna shuffle the results.
Limit one is gonna give us just the top one.
So essentially, we are getting one random vote
for a particular option.
Then, our Python function is gonna look
very much like our other ones.
Just grab the transactions, stop the cursor,
run the query with the option id as an argument,
and return cursor.fetchone.
Let's go and do this over in PyCharm.
All right, so we're here in PyCharm
and we've got the query created.
Select random vote.
It is equal to select '*' from votes
where the option id is equal to the value passed in.
Order by random, which is gonna shuffle the rows.
Limit one, which is gonna give us only one row,
essentially a random one.
It doesn't matter what order it's in
because we are shuffling them.
Then, we're gonna copy that, go down to the bottom,
and it's here, get random poll vote.
All we have to do is run the query.
So, we're gonna do cursor.execute.
We're gonna pass in the query and the option id,
making sure that it is a tophold
by putting that comma there.
And then return cursor.fetchone,
if I can type.
There we go.
Return and cursor.fetchone, again,
gives us that single row.
All right, that's really everything for this video.
As you can see, using the functions can be pretty simple,
but there's a lot of them
and you can use them in all sorts of places.
You should know some of the main functions
that are available in PostgreSQL,
so that's why I've left you the links
to the documentation in there
so you can have a look at that.
But I've also covered some of the main ones
over in the presentation as well.
Thank you guys for watching.
I hope you've enjoyed this video.
And I'll see you in the next one.