Storing Queries with Functions

[00:00:00]
>> All databases have this concept of functions and procedures, and then we'll talk about triggers, which have to relate to functions. So imagine you find yourself doing the same query over and over and over again across many parts of your application. You can basically use a function to kind of basically store that query as something that's kind of ready to go.

[00:00:25]
So let's, Yeah, let's say we're focusing on a recipe site with few ingredients, right? I don't know, I have a couple recipe books where basically, you live at home by yourself and you only have a pan and five ingredients. I like those books because, I don't live by myself, but I'm also very lazy, so I like those kind of recipes.

[00:00:49]
So imagine we have a filter for that on our recipe website. So we'd be selecting from recipe_ingredients, we'd have some sort of COUNT (r.title) BETWEEN 4 and 6. So let's just go ahead and run that. And you can see here, this is ingredients where that works, right, where we have four to six ingredients or something like that, right?

[00:01:22]
So by grouping by r.title, which is a unique key, we're a grouping by the ingredients, we're getting something that has four to six rows worth of ingredients. And then therefore, because we're grouping by the title, we can return that up here. So these are all recipes that have between four and six ingredients.

[00:01:40]
Feel free to pull that apart, but I promise you that's what it does. So let's say that we're doing our recipe website, users start hitting that specific page a lot, it gets really popular, it gets way more popular than the rest of our website. As a good product owner and trying to pivot into market fit, we just say let's do that entirely now, right?

[00:02:06]
This is our entire website, we're gonna focus on these low ingredient kind of recipes. So what we could do then is we could create a function that gives us that, right? So we can create this function called get_recipes_with_ingredients, so we can give it a between four and six, between two and three, between one and ten, right?

[00:02:31]
It gives us some flexibility and some reusability to it. This is what functions are for Postgres, it's one of the things that they can do. We'll see other things that they can do later, but that's kind of the idea here. Now, this is a language called PL/pgSQL, Programming Language Postgres SQLs, right?

[00:02:57]
So basically, it's a SQL kind of programming language. So you can see here, all of this looks really similar to SQL, right? There's a couple of new things like RETURN QUERY, that's not a part of SQL, but there's a bunch of stuff in here that's made to feel very SQL-like.

[00:03:19]
You can actually do JavaScript here, you can do Python, you can do Java, you can do Perl, and those are the ones I'm aware of. [LAUGH] It requires you to download an extension to Postgres and install it. I thought about doing JavaScript, because obviously, this is Frontend Masters, that would make some sense, but in the end, I ended up fighting the config so much, I don't want students to do that.

[00:03:43]
I haven't used it actually personally to do it with JavaScript, so actually, I'm just gonna show you how to do it with plpgsql, because this is actually built into Postgres directly. And it's SQL, it's an SQL, of course, it makes sense. But just know if you're really getting into this, you can do it directly with Java, JavaScript, and so on and so forth.

[00:04:02]
So let's talk about several of the things in here. You don't have to have the OR REPLACE, but I put this here so you can copy and paste in multiple times if you decide that you messed it up or wanna fix it or try different things. But you can put CREATE here, if it exists, it will fail, right?

[00:04:17]
That's what you need the OR REPLACE for. You can also drop them, so you can say drop FUNCTION get_recipes_with_ingredients, and it'll get rid of it. Okay, these are parameters, low and high, I think that makes sense. You can provide it with the low, the least amount ingredients you wanna select for, or the most, which would be high.

[00:04:36]
You have to give them a data type, in this case, they are integers. You have to identify what it's going to return, which is going to be a SETOF, which is, right, this is a set. It's a result set, of what? Of VARCHAR, they're gonna be strings. You have to tell what language, which we are doing plpgsql.

[00:04:59]
And then here, you have to return the AS basically as the function. Let's talk just a moment about dollar signs. So this is not just related to function, this is in general throughout all Postgres. It actually has this concept of dollar quotes, I'm not making it up, it's actually true.

[00:05:23]
It's basically a quote, so I can actually come in here, let's just show you, SELECT meta. And we were seeing here that you can do this with single quotes, right, cuz we were doing like JSON, but you can actually put dollar signs here. FROM, we put it on recipes, right?

[00:05:50]
And, WHERE meta is not null. Okay, so just to prove a point here, same thing as single quotes, kind of weird and neat, right? So why would you ever wanna use dollar quotes? It's sort of things like this, going back here. Where we're gonna have, we actually don't have any single quotes in here, but the nice thing about doing dollar quotes is you don't have to escape single quotes, right?

[00:06:19]
So if I was doing things where I was asking for single quotes inside of this, where I was asking for meta tags, right, I'd have to double quote those, cuz you're technically giving Postgres a string to invoke later, right? So by using the dollar quotes, we're getting away from having to escape our single quotes.

[00:06:39]
In other words, I really only use dollar quotes with functions, I really don't use them other places, but they are useful for that reason. Here, we're telling you where the beginning of our function is, where we're going to end our function. Notice that this has a semicolon, this has a semicolon, that's cuz we're doing a query here.

[00:06:57]
This part is actually here, the query. This is definitely not going to be a course on the PL/pgSQL language, cuz I don't know it very well. I have to go read a lot of Stack Overflow before I get these functions correct, but they are useful. All right, and then here, this is just pretty normal stuff.

[00:07:20]
The only thing here is we have low and high, so we actually are using variables directly in our query, but that seems fairly reasonable for us to use, right? Okay, so I'm just gonna copy and paste this whole thing, cuz I sincerely do not wanna write it all out.

[00:07:43]
So now I have a function, now how do we query it? How do we use it? Well, specifically with functions, you're gonna say, SELECT * FROM, and then you're gonna call the function, so get recipes_with_ingredients(4, 6). And notice, it's the same result set, Right? But now, it's flexible, I can say 2 and 4.

[00:08:17]
Turns out that's a lot of them, so that's 42 rows. But now we have this flexible thing, if we were using this everywhere in our website, this was a really important search facet for us. I'm sure you can imagine this being really useful to you. First thing I'm gonna say is this is just one of a couple of ways that you could accomplish this, but it's a way to accomplish this, right?

[00:08:47]
Let's make sure I covered everything I wanted to here. So yeah, PL/pgSQL, it's a full language, it can do a lot of stuff. It has for loops, it has conditionals, it's a Turing-complete programming language. Someone's probably written doom in it, right? I can't prove that, but I just assumed if it's a programming language, someone could port a doom to it.

[00:09:15]
Now, I'm scared to Google cuz it's probably true. [LAUGH] Yeah, it's very SQL-like in nature. Most of time, it reads very SQL-like. I'm showing you just kind of the surface here, but there's a lot of really powerful stuff you can do with it that frankly, I've not written enough Postgres functions to really tell you too much about.

[00:09:41]
But let me tell you, yeah, and then I was gonna show you here. So if you click on one of these links, PLV8, this is where you can actually use JavaScript as the language as opposed to the built-in Postgres one. You have to create an extension, you have to download it, there's a bunch of stuff to do.

[00:10:02]
And then it's like a flavor of JavaScript that has its own variables and stuff like that. I've never actually used it, but it is available. And then Python, there's a bunch of other stuff like that, we talked about dollar quotes. Just to show you really quick here in the notes, if you need to drop that function later, you can do it like this.

[00:10:22]
DROP FUNCTION, and then you just give it the name of the function and the call signature. You can technically overload these, right? So I can have a low, high, and medium, right? So you actually do have to tell it what parameters cuz you have to tell it the whole signature, but that's the function that you would run to delete it.

[00:10:44]
And then so the next thing I just wanna talk about briefly is do not go overboard with functions. So the first question I'm going to ask you is, if you write this huge function and then you store it in your Postgres like this, it's not going into a source control, right?

[00:11:03]
It's not going into the rest of your code base, that kinda sucks. So it introduces just like, it's not unobservable, but hard to observe behavior in your application. So if you're using it a lot throughout your different places in your application and it breaks, it's hard to track down that bug, because you expect your database to always be right.

[00:11:26]
So you're introducing developer error to your database, that is scary, that should be scary. There's ways you can get around that, you can store the functions locally and then can commit that to git, and then when you start up your database, it's pushed into your database. It gets messy though, right?

[00:11:46]
It adds indirection to your application, and makes it harder to reason about what's going on in your application. So I'm going to invite you to think about in things like, if you need to do something that you cannot do in your code and would not make sense to do in your code, by all means, this is another tool in your toolbox to use, right?

[00:12:06]
You could make your entire application totally just in Postgres functions if you wanted. Dear God, please don't. [LAUGH] As your potential future co-worker, just don't, just write code, right? They have done some really impressive things, if you've ever heard of Hasura. Hasura makes extensive use of Postgres functions to give you, basically, GraphQL at the Postgres layer.

[00:12:28]
It's really impressive stuff, I wouldn't wanna work on their codebase, personally. So that's my pitch for you. If you're doing something that really can only be done at the database layer or it makes a lot of sense to do at the database layer, think about using these. Otherwise, I'd say just put it in your Node code or your Python code or whatever.

[00:12:50]
Yeah, any questions?
>> If you're not writing the SQL code locally, how do you view the function again, if you wanna see what the function actually is?
>> There's a way to do it, I don't know though.
>> [LAUGH] I think you might be able to say just \d or something like that, I don't know.

[00:13:11]
I'd look in this this \?.
>> \df.
>> \df, so that'll give you the functions that exist, thank you. But if you actually wanna see the body of the function, you'd have to do some sort of querying to get it.
>> On the result part, if you, or the return signature, can you have it return multiple columns?

[00:13:36]
You just delineate that with a comma?
>> Yeah.
>> Okay.
>> So there's a bunch of stuff you can do that. You can actually write to the database with functions, we'll actually do that here in just a second. Basically, anything you can do with SQL, you can do with functions.