Functions vs. Procedures

[00:00:00]
>> You're gonna find that procedures and functions are very similar. But a procedure is a bit more limited in what can accomplish. I think procedures came first and then they made functions later that feels right for me to say I have no data on that. So Someone's probably gonna correct me but there are some.

[00:00:23]
There's a lot of similarities. Functions can definitely do more, almost everything that a procedure can do a function can do whereas the other way around is not true. So, a procedure is designed to do some sort of action, a function, its general purpose. It can do an action, it can just do some sort of like selecting, it can do basically anything that you can think of in code that you would run inside of a database.

[00:00:50]
That's what a function can do a procedure is much more scoped down to doing some individual action, let's just think about our recipes database. Let's say we have a lot of churn on ingredients for images can get created and deleted and sometimes images are set to null, right?

[00:01:06]
Let's say we wanted to tolerate that, that sometimes ingredients could be null. So we don't want to set, like the not null constraint on the database. But let's say that we don't want to keep that for very long. What we could do is we could have some sort of job right that would run in the background that would just go and find all of the images that had null set on them and then it would set it to some default image.

[00:01:31]
Right does that makes sense? So you have an ingredient it has an image, for some reason the image gets deleted, maybe it gets dropped out of our cache or something like that. So that at that point for a brief moment of time, whether that's, minutes, hours, days, something like that.

[00:01:47]
It'll have an ingredient that will have a null image and then at some point we can run a procedure that goes and sets all of those to be default .jpg. That's what a procedure would be fit for. That is what that's like the call sign for when a procedure could be useful.

[00:02:05]
So let's run this query right now, SELECT* WHERE image IS NULL. Right now we don't have any. But let's just go insert something into our database. So we're going to put venison in there. And now if we run that again, we now have venison that does not have an image.

[00:02:25]
All right so this is what I'm talking about maybe, maybe this would be another case where we'd want to allow people to insert things and we don't want to like burden the process too much with all this like setting things. I don't know I'm contriving a very contrived example here.

[00:02:42]
You were just in your code, say call this default .jpg and call it good and then make image not nullable. Bear with me for a moment, please. Now we're going to create a procedure. And it's going to be called set null ingredients to default. This is not going to be the programming language, this is actually going to be directly a query.

[00:03:05]
So we can say the language is SQL, it's not the PL/pgSQL. So all it's going to do is upgrade ingredients set image to default where image is null, right? It's really just running a quick query. You can use PL/pgSQL here, by the way, we just didn't need to.

[00:03:26]
So now I have a procedure here that I can call at any interval. We created our procedure here. We haven't called it yet we haven't done anything if we call that query again. Venison's still there and has no image. So again we could use PL/pgSQL here but we don't need to this is a simple query if you needed to do some sort of for loop or something like that you could you, you would need to use PL/pgSQL.

[00:03:52]
Now, we could have some sort of background job, we can have something in our database, or not our database that are like app that would reach out, there's a crom that we could have like a AWS lambda, that lambda is some sort of timer that would call this.

[00:04:08]
But anyway, something needs to call this function right here. Which is CALL, what did I call it, set null ingredients, null ingredient images to default. I'm just gonna copy that because I don't want to write it again. Call that it has no parameters. It could have had parameters.

[00:04:28]
We just didn't give it any right. We could have made it like, some sort of like, parameter to like what image that we're gonna set it to but we didn't, that's fine. We're just going to call that so I called the procedure. Now if I come back here and say this again it is not null and if I say select star from ingredients.

[00:04:56]
WHERE id equals 127, which is what we saw right there. You'll see now that it has a default jpg. So we call we use CALL instead of SELECT here, that's what procedures you CALL them, whereas with functions you SELECT them. So we can invoke this procedure whenever we want to.

[00:05:16]
And that is that's it. Now you might ask me when would you ever use a procedure instead of a function because everything I just showed you you could obviously do with a function. I don't know there's not really a good reason procedures have just been around for a long time so people expect them to be there.

[00:05:33]
There's a bunch of stuff that's built on procedures. They are more limited so if you believe in like the principle of least power right we try to use the least powerful thing that it can accomplish what you're trying to do procedure would be that. Frankly if it was me I probably wouldn't put any of this in my.

[00:05:50]
If I wouldn't make a procedure, I wouldn't make a function I would just have my code do it. I'd write some sort of lambda that would just query the database and then update all those things because again if this lives in your database it's not in your source control it's really hard to track that business logic outside of your application.

[00:06:07]
So I don't really suggest it, but I wanted to know it's there. It is kind of fun. And when I'm doing stupid projects for myself, I really like to write stupid things like this.
>> Can you call functions and starred procedures from other functions and starred procedures?
>> Yes you you can call you can do both.

[00:06:27]
I don't know, I assume you can call a function from a procedure. You can definitely call a procedure from a function. Vice versa I'm less sure. That seems like a cascading effect of terror.
>> Yeah.
>> But it's it sounds plausible. Yeah, question Dustin.
>> If a procedure or function has to update millions of rows, will it be a blocking operation?

[00:07:02]
>> It's let me answer that with a hand wavy response, which is it's not any different than just calling a function, right? So if you have some sort of function that's going to tie up your database doing it through a procedure a function is also going to tie up your database.

[00:07:18]
So the call the way it's called isn't going to make a difference in that particular case. The performance profile does not change coming from a procedure function or just a normal query. If you're in those kind of cases where you are like updating a lot of things and alter tables, and things like that.

[00:07:35]
You need to be more thoughtful than just writing a big query. You kind of have to do that piecemeal through code.