Having recently published this blog post about PLpgSQL dollar quoting, I thought it appropriate to follow-up with a post on function parameters. In this post, I will demonstrate 2 variations of a simple function, both of which accept a parameter. Yet, how the parameter is accessed and specified is different.
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.4
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!
I’ll use a couple of tables from the PostgreSQL practice DVD Rental database for the example queries below.
I have an arbitrary query that returns the MAX() ‘country_id’ for a city that we filter for with the LIKE predicate. I realize that without supplying a capital letter, an empty result set will likely be returned.
However, this functions primary purpose is for an example demonstration of how function parameters can be passed in and accessed in PLpgSQL.
Of importance…
I want to point out, the demonstrated versions of the example function is not safe from SQL Injection, as they both accept user input that is run against the database. Their sole purpose for this post is only to demonstrate how PLpgSQL functions can take and specify supplied parameters. Please use secure best practices in all of your code.
For starters, I could not say it better myself, than this direct passage from the Declaring Function Parameters section online:
“Parameters passed to functions are named with the identifiers $1, $2, etc. Optionally, aliases can be declared for $n parameter names for increased readability. Either the alias or the numeric identifier can then be used to refer to the parameter value.”This first version is likely familiar to most SQL programmers. Naming the function parameter – and type – in parenthesis is relatively common.
1 2 3 4 5 6 7 8 9 10 | CREATE OR REPLACE FUNCTION get_specific_country(IN c_letter CHAR) RETURNS SMALLINT AS $$ DECLARE c_id SMALLINT; BEGIN SELECT MAX(country_id) INTO c_id FROM city WHERE city LIKE ''||c_letter||'%'; RETURN c_id; END; $$ LANGUAGE PLpgSQL |
Calling function ‘get_specific_country()’ shows it works as intended:
1 2 3 4 5 6 7 8 9 10 11 | dvdrental=> SELECT get_specific_country('A'); get_specific_country ---------------------- 107 dvdrental=> SELECT get_specific_country('B'); get_specific_country ---------------------- 104 (1 row) |
Next, let’s write it like this:
1 2 3 4 5 6 7 8 9 10 11 | CREATE OR REPLACE FUNCTION get_specific_country(IN CHAR) RETURNS SMALLINT AS $$ DECLARE c_id SMALLINT; c_letter ALIAS FOR $1; BEGIN SELECT MAX(country_id) INTO c_id FROM city WHERE city LIKE ''||c_letter||'%'; RETURN c_id; END; $$ LANGUAGE PLpgSQL |
Notice in this version, only the data type, and type of parameter – IN – are present in the parenthesis. Calling this version produces the same results:
1 2 3 4 5 6 7 8 9 10 11 | dvdrental=> SELECT get_specific_country('A'); get_specific_country ---------------------- 107 (1 row) dvdrental=> SELECT get_specific_country('B'); get_specific_country ---------------------- 104 (1 row) |
In this version, using the ALIAS keyword, we are specifying that the parameter supplied is ‘c_letter’ for the identifier $1.
I’d love to see your comments and ideas on PLpgSQL programming (or SQL programming in general) on this post and any improvements, tips, and advice so feel free to provide a comment below.
My hope is through this blog post, you learned about PLpgSQL functions and how they access parameters. Be sure and visit the fantastic PLpgSQL section online for more 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.