In concert with writing quality open-source SQL blog posts, I have developed a yearning to become a well-versed PostgreSQL PLpgSQL programmer. What a perfect opportunity to share what I learn about PLpgSQL, on my blog, while building solid SQL skills. In this post, I will cover the SELECT INTO clause as part of a PLpgSQL function, focusing on the STRICT keyword for even more fine-tuned functionality.
Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.
OS and DB used:- Xubuntu Linux 18.04.2 LTS (Bionic Beaver)
- PostgreSQL 11.2
Self-Promotion:
If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!
Although I targeted a different SQL dialect, I wrote 3 fantastic blog posts exploring SELECT INTO I’d love for you to read. Feel free to visit them each in turn below.
- MySQL SELECT INTO Syntax Part 1 – Uses With Variables
- MySQL SELECT INTO Syntax Part 2 – Saving to OUTFILE with examples
- MySQL SELECT INTO Syntax Part 3 – DUMPFILE with examples
For the example queries in this post, I’ll use a couple of tables from the PostgreSQL practice DVD Rental database.
Suppose I have this query that returns all columns for any cities that start with ‘Z’:
1 2 3 4 5 6 7 8 9 10 11 12 13 | dvdrental=> SELECT * FROM city WHERE city LIKE 'Z%'; city_id | city | country_id | last_update ---------+--------------+------------+--------------------- 592 | Zalantun | 23 | 2006-02-15 09:45:25 593 | Zanzibar | 93 | 2006-02-15 09:45:25 594 | Zaoyang | 23 | 2006-02-15 09:45:25 595 | Zapopan | 60 | 2006-02-15 09:45:25 596 | Zaria | 69 | 2006-02-15 09:45:25 597 | Zeleznogorsk | 80 | 2006-02-15 09:45:25 598 | Zhezqazghan | 51 | 2006-02-15 09:45:25 599 | Zhoushan | 23 | 2006-02-15 09:45:25 600 | Ziguinchor | 83 | 2006-02-15 09:45:25 (9 rows) |
Arbitrarily, imagine we need a PLpgSQL function to return the ‘country_id’ column value for the above query. That function could be written as the following:
1 2 3 4 5 6 7 8 9 10 11 | CREATE OR REPLACE FUNCTION get_country() RETURNS SMALLINT AS $$ DECLARE c_id SMALLINT; BEGIN SELECT country_id INTO c_id FROM city WHERE city LIKE 'Z%'; RETURN c_id; END; $$ LANGUAGE PLpgSQL |
Upon calling this function, we are returned:
1 2 3 4 5 | dvdrental=> SELECT get_country(); get_country ------------- 23 (1 row) |
As we know, in the absence of an ORDER BY clause, the database will return results in an unordered fashion with no guarantees on the order. That being said, the purpose of this function is mainly used to demonstrate the next version of it. Likely this particular implementation would not be suited for anything production-ready or related in my opinion. However, since the base query returns multiple rows (potentially) yet only one row is stored in variable ‘c_id’, it is a working example to use with the STRICT keyword.
This next function definition version includes the STRICT keyword as part of the SELECT INTO clause:
1 2 3 4 5 6 7 8 9 10 11 | CREATE OR REPLACE FUNCTION get_country() RETURNS SMALLINT AS $$ DECLARE c_id SMALLINT; BEGIN SELECT country_id INTO STRICT c_id FROM city WHERE city LIKE 'Z%'; RETURN c_id; END; $$ LANGUAGE PLpgSQL |
When called, notice the difference in behavior from that of the first version:
1 2 3 | dvdrental=> SELECT get_country(); ERROR: query returned more than one row CONTEXT: PL/pgSQL function get_country() line 4 at SQL statement |
The specific error, query returned more than one row is returned because STRICT requires that no more than one row be returned from the query results that are the target of a variable of SELECT INTO.
As seen previously, without STRICT we get no run-time errors and variable ‘c_id’ is set with the first row from the unordered result set.
Of course, we can always re-write the query in a myriad number of ways to return just one row. (E.g., MAX(country_id), LIMIT 1, etc…). However, the focus of his post is to provide an example of STRICT‘s behavior when multiple rows are returned.
The documentation hints at catching this type of error with an EXCEPTION block so I’ll borrow heavily from that resource in the section below.
After dropping the previous version of function ‘get_country()’, I’ll create the version below to catch the TOO_MANY_ROWS condition error:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | CREATE OR REPLACE FUNCTION get_country() RETURNS SMALLINT AS $$ DECLARE c_id SMALLINT; BEGIN SELECT country_id INTO STRICT c_id FROM city WHERE city LIKE 'Z%'; RETURN c_id; EXCEPTION WHEN TOO_MANY_ROWS THEN RAISE EXCEPTION 'More than one row returned in query result set.'; END; $$ LANGUAGE PLpgSQL |
Now when calling ‘get_country()’, I am returned the specific message from the EXCEPTION block:
1 2 3 | dvdrental=> SELECT get_country(); ERROR: More than one row returned in query result set. CONTEXT: PL/pgSQL function get_country() line 9 at RAISE |
There is so much to learn in the world of PLpgSQL programming. By all means, help me along the way with any feedback, comments, suggestions, and other useful information.
Like what you have read? See anything incorrect? Please comment below and thanks for reading!!!
Explore the official PostgreSQL 11 On-line Documentation for more information.
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.