{"id":101334,"date":"2024-02-15T22:09:31","date_gmt":"2024-02-15T22:09:31","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=101334"},"modified":"2024-04-19T13:47:56","modified_gmt":"2024-04-19T13:47:56","slug":"functions-and-procedures-learning-postgresql-with-grant","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/postgresql\/functions-and-procedures-learning-postgresql-with-grant\/","title":{"rendered":"Functions and Procedures: Learning PostgreSQL with Grant"},"content":{"rendered":"

This is part of an ongoing series of post by Grant as he takes his vast knowledge of SQL Server and applies it to adding PostgreSQL and shares it with you so you can skip learn from his triumphs and mistakes. For more you can go to the Learning PostgreSQL with Grant<\/a> series home page<\/strong><\/em><\/p>\n\n

One of the most useful constructs in SQL Server is the stored procedure<\/a>. It gives you a way to do several things. First up, you can store code within the database. Next, you can parameterize queries so that you\u2019re not hard coding or generating ad hoc queries every time you want to call them. You can put in transaction handling, multiple result sets, security and more. They truly are a useful tool. So, of course, I went straight to CREATE PROCEDURE<\/a> in PostgreSQL.<\/p>\n

I begin reading about procedures in PostgreSQL and I\u2019m suddenly hit with a revelation. Procedures can\u2019t return result sets. They can\u2019t return anything except INOUT<\/code> parameter values (more on that in a bit). What the heck do we do to return results? Thankfully, Ryan Booz pointed me in the right direction, CREATE FUNCTION<\/a>.<\/p>\n

We have functions in SQL Server. They are of course scalar, table valued or multi-statement table values (AKA, sort of evil<\/a>). However, they\u2019re not that similar in form or function to SQL Server\u2019s procedures, so what\u2019s going on in PostgreSQL?<\/p>\n

The Differences Between Functions and Procedures<\/h2>\n

In PostgreSQL, CREATE FUNCTION<\/code> predates CREATE PROCEDURE<\/code>, by a considerable margin. Procedures were added in PostgreSQL 11 (we\u2019re on 16 as of this writing, and 17 is on the roadmap<\/a> for later this year!). If you look at the syntax in the links already provided, you\u2019re going to notice quite a few similarities. This is because these two constructs are similar. However, there are a few differences. Let\u2019s talk about them.<\/p>\n

First up, as was already mentioned, a procedure in PostgreSQL cannot return a result set. A function can return a result set. In fact, functions can return multiple result sets. Further, this being PostgreSQL, those result sets are objects, which means you can return, not simply a set of columns and rows (tuples), but a table, a cursor (the good kind, and yes, there\u2019s a good kind of cursor, at least in PostgreSQL, it\u2019ll take another article to explain it), other things. Both procedures and functions do allow for output parameters, what they call INOUT<\/code> parameters. So, why do we need procedures?<\/p>\n

The next big difference between procedures and functions is that functions cannot control transactions. Yeah, you read that correctly, you can\u2019t control transactions within functions. Now, don\u2019t get this wrong. Functions are absolutely subject to transactions. For example, if you write a function that inserts data into a table, and there\u2019s an error, the function will get rolled back. If there were multiple statements within the function, they all get rolled back. The function takes part in the greater transaction that called the function. If that transaction gets committed, so does any side effects that occurred in the function. If that transaction is rolled back, so are all the function calls within it. Procedures on the other hand, you can COMMIT<\/code> and ROLLBACK<\/code> transactions from within the procedure.<\/p>\n

The final major difference is how they\u2019re called. A function is called from a SELECT<\/code> statement. 50% of the SQL Server people reading this just groaned out loud. The other 50% just fist-pumped because they\u2019ve always wanted the ability to JOIN<\/code> procedures. A procedure is called from, are you read, the CALL<\/code> statement. The biggest difference between these is the implications of using a SELECT<\/code> versus a CALL<\/code>, but really that\u2019s about it. Oh, I know, it can be a huge difference in the long run, but for our (remember our motto) introductory purposes, it\u2019s a relatively benign process.<\/p>\n

There are several other minor differences, especially in how they get used, but these are the three main differentiators. In short, if you want a result set, beyond just a single row from INOUT<\/code> parameters, you need to use a function. If you need to control transactions within the code, and you\u2019re not returning a result set, you need to use a procedure.<\/p>\n

Note: In the sample database I\u2019ve created as a part of this ongoing series, I created a couple of schemas and organized my tables within them. If you wish to execute the code or look at the data structures, the code is in my ScaryDBA\/LearningPostgreSQL <\/code>repository here<\/a>. The objects and database you will need can be created\/reset using the\u00a0CreateDatabase.sql\u00a0script, then adding sample data using the\u00a0SampleData.sql script. The rest of the code needed for this article is in the 12_FunctionsAndProcedures<\/code> folder.<\/p>\n

Now, let\u2019s see these two things in action.<\/p>\n

CREATE FUNCTION<\/h2>\n

The core syntax for creating a function isn\u2019t all that surprising. First, I\u2019ll create a function that returns a result set. Second, I\u2019m going to create a function that inserts a row into a table. I\u2019m using this as an example, rather than returning a result set, so that I can use the exact same code within the procedure example below. This allows us to compare apples to apples.<\/p>\n

CREATE OR REPLACE\r\nFUNCTION radio.radiodetails (radioid INT)\r\nRETURNS TABLE(radio_id int,\r\nradio_name varchar(100))\r\nAS $$\r\nSELECT\r\n\tr.radio_id,\r\n\tr.radio_name\r\nFROM\r\n\tradio.radios AS r\r\nWHERE\r\n\tr.radio_id = $1 $$\r\nLANGUAGE SQL;<\/pre>\n
The core syntax is of course CREATE OR REPLACE FUNCTION<\/code>, and from there I\u2019ll break it down a bit. Obviously, I supplied a schema and a name for the function, radio.radiodetails<\/code>. If you don\u2019t supply a schema, it\u2019ll go to whichever one you\u2019re defaulting to at the moment.<\/p>\n
Next, I supplied a parameter, radioid<\/code>. Within the code, in this example, I didn\u2019t use the name. Instead, I referenced the ordinal position of the parameter using $1. In typical production code that would need to be maintained, I wouldn\u2019t use the ordinal position like this because it\u2019s less clear than using the parameter names.<\/p>\n
I could modify the code to look like this:<\/p>\n
\u2026r.radio_id = radioid \u2026<\/code><\/p>\n
It is possible to skip the name completely. I could define just the data type of the parameter like this:<\/p>\n
\u2026FUNCTION<\/strong> radio.radiodetails (INT<\/strong>)\u2026<\/code><\/p>\n
Then, I would be forced to use the ordinal position of the parameter in the code. Again, this makes for unclear code, so it\u2019s certainly not a practice I would follow.<\/p>\n
By default, parameters are for input to the function. However, you can have output parameters, you just have to define them as such. You can also have parameters that are both input and output. I could completely rewrite the function above like this to return the same information:<\/p>\n
CREATE OR REPLACE\r\nFUNCTION radio.radiodetails2 (INOUT radioid INT, \r\n                              OUT radioname varchar(100))\r\nAS $$\r\nSELECT\r\n\tr.radio_id,\r\n\tr.radio_name\r\nFROM\r\n\tradio.radios AS r\r\nWHERE\r\n\tr.radio_id = radioid $$\r\nLANGUAGE SQL;<\/pre>\n
I had to give it a new name because you can\u2019t change the RETURN<\/code> type of a function. I would have had to drop the function and then create it again from scratch.<\/p>\n
In the original function, I defined the RETURN<\/code> as a TABLE<\/code> and then supplied the table definition. If the query in question is returning multiple rows, this is the way to get it done.<\/p>\n
Finally, I defined the language as SQL. You can also make functions in c, internal, or user defined types. Most of the time, queries being queries, it\u2019s likely to be SQL.<\/p>\n
To execute the function, I can just call it in a SELECT<\/code> statement:<\/p>\n
SELECT * FROM radio.radiodetails(2);<\/code><\/p>\n
This will return the parameter values as a result set:<\/p>\n
radio_id|radio_name|\r\n-------+----------+\r\n      2|FT3D      |<\/pre>\n
And if I wanted to modify data in the database, I could create a function like this:<\/p>\n
CREATE OR REPLACE\r\nFUNCTION radio.addantenna (antennaname varchar(100),\r\nmanufacturerid int,\r\nconnectortypeid int)\r\nRETURNS VOID\r\nAS $$ \r\nINSERT\r\n\tINTO\r\n\tradio.antenna\r\n(antenna_name,\r\n\tmanufacturer_id,\r\n\tconnectortype_id)\r\nVALUES\r\n(antennaname,\r\nmanufacturerid,\r\nconnectortypeid)\r\n$$ \r\nLANGUAGE SQL;<\/pre>\n
The only thing I did here is have it set up as RETURNS VOID<\/code> since there is no output when this function is called. However, calling the function does result in, to my SQL Server eyeballs, some odd syntax:<\/p>\n
SELECT\r\n\t*\r\nFROM\r\nradio.addantenna('Stubby',\r\n\t4,\r\n\t2);<\/pre>\n
You may also see an output from this query like the following (this is the output when calling this function from DBeaver):<\/p>\n
addantenna|\r\n----------+\r\n          |<\/pre>\n
While that is the right way to reference the function, calling SELECT<\/code> in order to INSERT<\/code> data just feels weird.<\/p>\n
There is a lot more detail to the behaviors and definitions around functions, but that\u2019s a good start. Now, let\u2019s see how procedures work.<\/p>\n
CREATE PROCEDURE<\/h2>\n
Procedures in PostgreSQL are really just a type of function, but with the added ability to deal with transactions internally. As close as they are to functions, there are a few differences. Here\u2019s the syntax to create a copy of the code that inserts into the antenna table:<\/p>\n
CREATE OR REPLACE\r\nPROCEDURE radio.newantenna (\r\nantennaname varchar(100),\r\nmanufacturerid int,\r\nconnectortypeid int)\r\nAS $$ \r\nINSERT\r\n\tINTO\r\n\tradio.antenna\r\n(antenna_name,\r\n\tmanufacturer_id,\r\n\tconnectortype_id)\r\nVALUES\r\n(antennaname,\r\nmanufacturerid,\r\nconnectortypeid)\r\n$$ \r\nLANGUAGE SQL;<\/pre>\n
The only real difference here is the removal of the RETURNS<\/code> clause. Otherwise, it\u2019s identical code. You can return values from a procedure, but only through output parameters, which are defined the same way as with a function.<\/p>\n
To see transactions in action, we\u2019ll make a few modifications to the procedure:<\/p>\n
CREATE OR REPLACE\r\nPROCEDURE radio.newantenna (\r\nantennaname varchar(100),\r\nmanufacturerid int,\r\nconnectortypeid int)\r\nAS $$ \r\nBEGIN\r\nIF manufacturerid < 1 THEN \r\n\tROLLBACK;\r\nELSEIF connectortypeid < 1 THEN \r\n\tROLLBACK;\r\nELSE\r\n\tINSERT\r\n\t\tINTO\r\n\t\tradio.antenna\r\n\t(antenna_name,\r\n\tmanufacturer_id,\r\n\tconnectortype_id)\r\n\tVALUES\r\n\t(antennaname,\r\n\tmanufacturerid,\r\n\tconnectortypeid);\r\nEND IF;\r\nEND\r\n$$ \r\nLANGUAGE plpgsql;<\/pre>\n
The biggest change is where I redefined the LANGUAGE<\/code> to plpgsql because I want to use IF statements in the code and they\u2019re not supported in straight SQL. Other than that, you can see that I\u2019m taking charge of the transaction to ROLLBACK<\/code> if values below one is entered for the two ID parameters. That is the big difference between functions and procedures.<\/p>\n
Then, to execute the procedure, we use the CALL statement like this:<\/p>\n
CALL radio.newantenna('2 Meter short', 2, 2);<\/pre>\n
That will add one row to the antenna table.<\/p>\n
Conclusion<\/h2>\n
While there are some odd things, like using SELECT<\/code> with an INSERT<\/code> function, overall the behavior of functions and procedures is relatively straightforward and easy enough to understand.<\/p>\n
Functions return a result set, where procedures do not, except output parameter values. Procedures can control transactions where functions do not. With that in mind, generally, I\u2019m assuming the rule is to use functions except where you do need to control the transaction and then use procedures.<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
One of the most useful constructs in SQL Server is the stored procedure. It gives you a way to do several things. First up, you can store code within the database. Next, you can parameterize queries so that you\u2019re not hard coding or generating ad hoc queries every time you want to call them. You……<\/p>\n","protected":false},"author":221792,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143534],"tags":[158977,158976,158978,159066],"coauthors":[6785],"class_list":["post-101334","post","type-post","status-publish","format-standard","hentry","category-featured","category-postgresql","tag-learningpostgresqlwithgrant","tag-planetpostgresqlgrantfritchey","tag-postgresql","tag-postgresql-101-webinar-sidebar"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/101334","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221792"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=101334"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/101334\/revisions"}],"predecessor-version":[{"id":102181,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/101334\/revisions\/102181"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=101334"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=101334"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=101334"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=101334"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}