Learn how you can use stored procedures to give a name to any of your SELECT queries and make them reusable.
If you work with any SQL database, you might find yourself typing the same SELECT queries very often. Sometimes they are short and don’t take a lot of time to create. The other times, there’s a couple of joins, aggregation, order and you might not want to lose that function when you close the query console.
It’s very convenient to save your SELECT under any name you like and use it later to get immediate results.
Saving a simple SELECT query as a function
For demonstration purposes, let’s imagine you have the table
app_user which stores the users of your application.
If you want to get the total count of your users, you’ll write something like
SELECT count(*) FROM app_user;
To give this query a name, you can save it as a stored function.
create or replace function count_app_users() returns table ( total bigint ) language plpgsql as $$ begin return query ( select count(*) from app_user ); end; $$;
Let’s break it down in detail:
- In the first line we declare a function and give it the name
create or replacestatement is used in case you already have this function and want to update it. If you’re sure it doesn’t exist, you can just use
- Then we explicitly define the return type, which is
tablefor all SELECT queries
- In the brackets, we define the columns of the return table and their types.
- You can consider
language plpgsqland the next couple of lines as a boilerplate
- The query is placed inside of the
return query(...)statement which is wrapped between
Calling the named SELECT query
After we’ve created a function, we can call it by its name.
SELECT * FROM count_app_users();
Such syntax is determined by the fact that our function returns a table. All we do is select everything that this table has in it.
Passing arguments into the function
Now, let’s say that we want to count only the users created after a specific date.
To do that, we need to use the parameter inside of the SELECT query. In the function signature, we’ll add the parameter names and types. Then, inside of the SELECT, we can refer to the provided values.
create or replace function count_app_users(after timestamp) -- declaring the "after" parameter returns table ( total bigint ) language plpgsql as $$ begin return query ( select count(*) from app_user where created_at > after -- using the "after" parameter ); end; $$;
Now, you can add a date to the function call and get a
count of users created after a specific date.
SELECT * FROM count_app_users('2020-11-01');
Deleting a stored function in PostgreSQL
If you try to call the function
count_app_users without any arguments, it will still work. This happens because the function is determined not only by its own name but the parameter names as well.
So, now we have two functions
count_app_users(after) stored in the database.
If you don’t need one of them, you can drop it like this:
DROP function count_app_users();
The syntax is similar to dropping a table and is very straightforward.
Retrieving a stored function in PostgreSQL
count example is oversimplified, but if you store a longer function, you might want to get the content of the function to edit it in the future.
To do this, you just need to know the name of the function
SELECT prosrc FROM pg_proc WHERE proname = 'count_app_users';
This returns all the text between
Dealing with common errors
The first error that you might get when saving the SELECT query into a function looks something like this
ERROR: query has no destination for result data Hint: If you want to discard the results of a SELECT, use PERFORM instead. Where: PL/pgSQL function count_app_users() line 3 at SQL statement
Most likely this happened because you forgot to wrap the SELECT query in the
return query(...) statement.
Then, you might see the error which is related to the way you’re calling the stored function in PostgreSQL
ERROR: cannot use RETURN QUERY in a non-SETOF function
Probably you’ve tried to call the function like
As the function returns a table and not a single value, you should call it the same way as you would select the data from any other table
SELECT * FROM count_app_users();
It’s very convenient to be able to give names to your
SELECT queries and save them.
We’ve covered a very simple example, but you’re not limited in any way in the complexity of the
SELECT that you want to save.
Use joins, aggregations, ordering, and filtering, and never have to write the same queries over and over again!