I’m quite intrigued when it comes to PostgreSQL’s procedural language, PLpgSQL (honestly, SQL stored procedures and functions in general). I’ve grown fond of writing functions on the ‘server-side’, exploring their use and benefit. But what are some things you can do with simple(er) functions? Let’s learn together.
OS and DB used:
- Xubuntu Linux 16.04.3 LTS (Xenial Xerus)
- PostgreSQL 10.4
*Note: This blog post is inspired from what I’ve studied and learned in this great YouTube Video on MySQL Stored Procedures. Although a different database system is used in this channel’s videos, I still wanted to give a shout out to it. Make sure and visit it if you are interested in some great MySQL videos over there.
Shameless Plug:
I published a similar blog post, PostgreSQL Functions — Reusable Bits Of Code., over on Medium that shares some similarities (in regards to the subject matter at least) with this blog post so, by all means, check it out!
What do I want to accomplish with PLpgSQL functions?
I track, store, and analyze all of my exercise hiking stats and regularly query that data set to measure progress (Or lack thereof!).
That’s a good place to start IMO.
- Write a PLpgSQL function that returns the number of times I hiked a specified trail in a particular month. Say for the month of May.
- This function will require two 2 parameters: a trail name and a month number (so it’s dynamic and not limited to just May.)
Here are the descriptions for the involved tables:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | fitness> \d hiking_stats; +-------------------+------------------------+-----------------------------------------------------------------+ | Column | Type | Modifiers | |-------------------+------------------------+-----------------------------------------------------------------| | hike_id | integer | not null default nextval('hiking_stats_hike_id_seq'::regclass) | | day_walked | date | | | cal_burned | numeric(4,1) | | | miles_walked | numeric(4,2) | | | duration | time without time zone | | | mph | numeric(2,1) | | | additional_weight | boolean | default false | | trekking_poles | boolean | default false | | shoe_id | integer | | +-------------------+------------------------+-----------------------------------------------------------------+ Indexes: "hiking_stats_pkey" PRIMARY KEY, btree (hike_id) Referenced by: TABLE "hiking_trail" CONSTRAINT "trail_hiked_th_id_fkey" FOREIGN KEY (th_id) REFERENCES hiking_stats(hike_id) ON UPDATE CASCADE TABLE "weight" CONSTRAINT "wh_fk" FOREIGN KEY (wh_id) REFERENCES hiking_stats(hike_id) ON UPDATE CASCADE |
The day_walked column from table hiking_stats can be filtered for the month with the extract() function.
Trail names and surface material are in table trail_route:
1 2 3 4 5 6 7 8 9 10 11 12 | fitness> \d trail_route +------------------+---------+-----------------------------------------------------------------+ | Column | Type | Modifiers | |------------------+---------+-----------------------------------------------------------------| | trail_id | integer | not null default nextval('trail_route_trail_id_seq'::regclass) | | name | text | | | surface_material | text | | +------------------+---------+-----------------------------------------------------------------+ Indexes: "trail_route_pkey" PRIMARY KEY, btree (trail_id) Referenced by: TABLE "hiking_trail" CONSTRAINT "trail_hiked_tr_id_fkey" FOREIGN KEY (tr_id) REFERENCES trail_route(trail_id) ON UPDATE CASCADE |
And we have a linking table here:
1 2 3 4 5 6 7 8 9 10 | fitness> \d hiking_trail; +----------+---------+-------------+ | Column | Type | Modifiers | |----------+---------+-------------| | th_id | integer | | | tr_id | integer | | +----------+---------+-------------+ Foreign-key constraints: "trail_hiked_th_id_fkey" FOREIGN KEY (th_id) REFERENCES hiking_stats(hike_id) ON UPDATE CASCADE "trail_hiked_tr_id_fkey" FOREIGN KEY (tr_id) REFERENCES trail_route(trail_id) ON UPDATE CASCADE |
This function will get us started:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | CREATE OR REPLACE FUNCTION trail_count( p_trail_name VARCHAR(50), p_month_hiked INTEGER) RETURNS INTEGER AS $count_em_up$ DECLARE v_total_hikes INTEGER; v_trail_name VARCHAR(50) := p_trail_name; v_month_hiked INTEGER := p_month_hiked; BEGIN SELECT COUNT(hs.day_walked) INTO v_total_hikes FROM hiking_stats AS hs INNER JOIN hiking_trail AS ht ON hs.hike_id = ht.th_id INNER JOIN trail_route AS tr on ht.tr_id = tr.trail_id WHERE extract(month FROM hs.day_walked) = v_month_hiked AND UPPER(tr.name) = UPPER(v_trail_name); RETURN v_total_hikes; END; $count_em_up$ LANGUAGE PLpgSQL; |
These are trail name choices to pass as the p_trail_name parameter in the function call (Note: All trail names are fictitious and only my ‘generic’ name for tracking purposes :-)):
1 2 3 4 5 6 7 8 9 10 11 | fitness=> SELECT name from trail_route; name ------------------------ Tree Trail-short Tree Trail-extended Lake Path Loop Yard Mowing House-Power Line Route Sandy Trail-Drive West Boundary (7 rows) |
Let’s call the function and test that it works.
I’ll pass in 'Sandy Trail-Drive' and the number 5 (for the month of May):
1 2 3 4 5 | fitness=> SELECT trail_count('Sandy Trail-Drive', 5); trail_count ------------- 14 (1 row) |
We can verify these results are correct, using the same query (from function trail_count) over in psql, with proper context of course:
1 2 3 4 5 6 7 8 9 10 11 12 13 | fitness=> SELECT COUNT(hs.day_walked) fitness-> FROM hiking_stats AS hs fitness-> INNER JOIN hiking_trail AS ht fitness-> ON hs.hike_id = ht.th_id fitness-> INNER JOIN trail_route AS tr fitness-> on ht.tr_id = tr.trail_id fitness-> WHERE extract(month FROM hs.day_walked) = 5 fitness-> AND fitness-> UPPER(tr.name) = UPPER('Sandy Trail-Drive'); count ------- 14 (1 row) |
Perfect…
Let’s test again with another trail. In April this go around:
1 2 3 4 5 | fitness=> SELECT trail_count('House-Power Line Route', 4); trail_count ------------- 1 (1 row) |
Since trail_count() is converting with the UPPER() function, in the second predicate of the WHERE clause, on the trail_name column, letter-case is not an issue.
All submitted lower-case, upper-case, and mixed case trail names still return correct results:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | fitness=> SELECT trail_count('house-power line route',5); trail_count ------------- 6 (1 row) fitness=> SELECT trail_count('House-Power Line Route',5); trail_count ------------- 6 (1 row) fitness=> SELECT trail_count('HOUSE-POWER LINE ROUTE',5); trail_count ------------- 6 (1 row) |
A convenient option is the \set meta-command, for user-defined variable assignment within psql.
(Check out Use shortcuts in psql to make your life easier with the \set meta-command. I wrote if you like. It’s super cool!)
Once set, these variables can be passed as parameters to the function as well:
1 2 3 4 5 6 7 | fitness=> \set trail 'House-Power Line ROUTE' fitness=> \set mnth 5 fitness=> SELECT trail_count(:'trail',:'mnth'); trail_count ------------- 6 (1 row) |
Works like a charm.
An alternate variation is to assign the initial function call to its own variable:
(*Note: We have to escape any single quotes with a single quote, in the function call)
1 2 3 4 5 6 | fitness=> \set what_count 'SELECT trail_count(''house-power LINE route'', 5);' fitness=> :what_count trail_count ------------- 6 (1 row) |
Can’t complain about that, can we?
Now that I know how many times I hiked a certain trail, what about the date I burned the most calories for a specific trail?
That seems like a great candidate for a PlpgSQL function too don’t you think?
Let’s work on that.
This function will get us the date for the trail hiked with the most calories:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | CREATE OR REPLACE FUNCTION most_calories( p_trail_name VARCHAR(50)) RETURNS DATE AS $burn_them_calories$ DECLARE v_trail_name VARCHAR(50) := p_trail_name; v_what_day DATE; BEGIN SELECT hs.day_walked INTO v_what_day FROM hiking_stats AS hs INNER JOIN hiking_trail AS ht ON hs.hike_id = ht.th_id INNER JOIN trail_route AS tr ON ht.tr_id = tr.trail_id WHERE UPPER(tr.name) = UPPER(v_trail_name) ORDER BY hs.cal_burned DESC LIMIT 1; RETURN v_what_day; END; $burn_them_calories$ LANGUAGE PLpgSQL; |
Let’s retrieve the date for the most calories I burned on the 'Sandy Trail-Drive' trail:
1 2 3 4 5 | fitness=> SELECT most_calories('Sandy Trail-Drive'); most_calories --------------- 2018-05-22 (1 row) |
While that does work, it’s rather bland don’t you think?
How about something more informative, with a message?
However, the return type of function most_calories is of type DATE.
Therefore, we need to change from a DATE type since we are returning more than that with a message.
Also, I’ll DECLARE an additional variable to store the number of calories returned from the query.
First, I’ll DROP (not shown) the existing version of most_calories or the attempt (to rewrite) will error out since the original return type is changing.
This version of the function should do it:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | CREATE OR REPLACE FUNCTION most_calories( p_trail_name VARCHAR(50)) RETURNS TEXT AS $burn_them_calories$ DECLARE v_trail_name VARCHAR(50) := p_trail_name; v_what_day DATE; v_total_calories NUMERIC(4,1); v_message TEXT; BEGIN SELECT hs.day_walked, hs.cal_burned INTO v_what_day, v_total_calories FROM hiking_stats AS hs INNER JOIN hiking_trail AS ht ON hs.hike_id = ht.th_id INNER JOIN trail_route AS tr ON ht.tr_id = tr.trail_id WHERE UPPER(tr.name) = UPPER(v_trail_name) ORDER BY hs.cal_burned DESC LIMIT 1; SELECT 'You burned '||v_total_calories||' calories, hiking trail: '||v_trail_name||' on date: '||v_what_day||'. Great Job!' INTO v_message; RETURN v_message; END; $burn_them_calories$ LANGUAGE PLpgSQL; |
Let’s call it and see what is provided now:
1 2 3 4 5 | fitness=> SELECT most_calories('Sandy Trail-Drive'); most_calories -------------------------------------------------------------------------------------------- You burned 407.8 calories, hiking trail: Sandy Trail-Drive on date: 2018-05-22. Great Job! (1 row) |
Let’s run a couple more:
1 2 3 4 5 | fitness=> SELECT most_calories('Tree Trail-extended'); most_calories ---------------------------------------------------------------------------------------------- You burned 311.2 calories, hiking trail: Tree Trail-extended on date: 2018-05-02. Great Job! (1 row) |
1 2 3 4 5 | fitness=> SELECT most_calories('west boundary'); most_calories ---------------------------------------------------------------------------------------- You burned 380.5 calories, hiking trail: west boundary on date: 2018-06-26. Great Job! (1 row) |
My hope is this blog post provides you with some ideas to get started writing your own PLpgSQL functions. These functions provide a plethora of benefits, which cannot possibly be covered in one blog post. So far, I have only scratched the surface of the capabilities but this is an area I am deeply interested in and will continue to explore.
So help me grow and expand even more.
I’d love to know your suggestions on better SQL functions, procedures, triggers, or routines for server-side programming practices and those functions you already leverage.
Feel free to comment below and many thanks for visiting and reading!
Be sure and visit the stellar online PostgreSQL 10 Documentation for any related questions.
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
Have I mentioned how much I love a cup of coffee?!?!
To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, is performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.