# SQL built-in functions: getting a random voter
Most SQL database systems provide functions you can run inside a query to perform all sorts of things.
For example, in PostgreSQL you can do this:
And that gives you a random number between
SELECT COUNT(id) FROM polls;
We can also use the function, COUNT, to tell us how many rows for the column
id exist in
You can use
COUNT with any row result. For example,
COUNT(*) is totally valid and is normally the way the function is used.
Other popular aggregate functions are:
You can also create your own functions (opens new window) using SQL (in a very similar way to what we'd do using Python). However, that is a very advanced topic and we won't be looking into it in this course.
However do remember that the reason to define SQL functions is the same as for Python functions: to organise and reuse code. If you find yourself repeating yourself often within a database, you can create a function that will stay in the database and can be used in your queries. Similarly if you create a Python function or variable to execute or store a query definition, sometimes it can be useful to do this in the database instead!
# Using functions in our app queries
We'll need to use a function in our app. That's because in order to get a random person who voted for an option (i.e. the "winner"), we'll use the
This is how you select a random row from a table in PostgreSQL. We're adding this to
SELECT_RANDOM_VOTE = "SELECT * FROM votes WHERE option_id = %s ORDER BY RANDOM() LIMIT 1;"
ORDER BY RANDOM() shuffles the rows. Then doing
LIMIT 1 just gets the first one, after they've been shuffled.
Remember though that this query needs a value to be passed to it.
Let's define our
get_random_poll_vote() function too:
def get_random_poll_vote(connection, option_id): with connection: with connection.cursor() as cursor: cursor.execute(SELECT_RANDOM_VOTE, (option_id,)) return cursor.fetchone()