In an earlier video, we looked just for a second at a function called "Generate Series". I think we used it to generate numbers one through five in characters A through E, maybe, if that sounds familiar. That generate series function is interesting because it doesn't just return a single value like most functions do. Instead it returns a set of rows. It is in fact part of a category of functions called a set returning function. These functions, we're just gonna look at a few. There are several.

We're going to look at a few because they're fascinating and I think super cool. These functions return a set of rows from which you can select. You can select from them, you can union them, you could join them in. I'm just gonna show you the basic select functions because we've already done a whole lot about joining. If you need to join, you can use one of these set returning functions to generate or produce some data for you.

I have all of these queries already typed out and we're just gonna kind of scroll up and down because you don't want to watch me type all of these. We're starting with generate series, but instead of numbers, we're doing dates. We're gonna take the string, cast it to a date, say gimme all of the dates between 01-01 and 01-10 with a step of one day. If we run that, you'll see it comes back as not a date, which is fine because you can just throw it into a date there or you could change the step and say, let's step all the way through January, three days at a time, 1, 4, 7, 10. You can change the step. Of course, you can change the step on numbers as well, which might be interesting. You could do zero to a hundred at evens only and cast it to an int. There you go 0, 2, 4, 6, 8, all the way up to 100. Very cool, very interesting.

Let's keep going. Coming down here, we have an array. You might have stored an array as, let's say a list of tags. I think that's one example that we've talked about. You can turn that array into a set of rows by un-nesting it, and you can give it an alias as elements. You could say as tag name and that column becomes tag name, which you can then use to join in or filter on or anything like that. There's also an option to have an ordinality. If we do the same type of things, we have unnest array first, second, third with ordinality. Then you can give it both a table alias and column aliases.

If we were to just say, let's just select * from that, we have the element and the ordinality, and this is an auto incrementing integer here. You could, you could come back up here and if you wanted to switch the order back like we had it, you would say ordinality and element. If you drop this out, the ordinality is gone. We don't have an ordinality. Ordinality is super nice when you need a unique ID for this made up, generated set of rows here.

Moving on, we have JSON to record set. I'll show you what it looks like because it's very nice. It turns a JSON array of objects into a proper table against which you can, of course, continue to query or join or do whatever you want. Again, we have specified the table alias here, the table, or I'm sorry, the column name, but then also the column type, which can be nice and probably quite important in many cases. This is JSON to record set. We also have JSON B to record set. Here we're taking this string and we're casting it to JSON B.

In reality, you probably have this stored in a column as JSON B. Notice up here, we didn't cast it to anything. We just passed through a string of JSON, which, if you'll remember back to the data types videos, that is actually what the JSON column type is. It just strictly stores the JSON string. It doesn't do any parsing where JSON B does that parsing. If we run that, that works just fine. We are gonna cover a lot more JSON in the JSON module. There's so much to do in that JSON module, but I just wanted to show you these first few record set ones.

Let's keep going on strings. This one is very cool because you can do a regexp match and turn it into a set of rows. We have our sentence here and we're saying beginning of word, word like character four times, end of word, globally, and that matches over and lazy because those are the two that have four characters. If we changed it to five, you'd get quick, brown, and jumps. Staying with regexp, you can do captures. In this case, we have a regimented, a nicely formatted string and this is very common. You have a nicely formatted string from some other system that you want to extract data out of. In this case, we are saying, all right, let's match N-A-M-E colon space, and then I want to start capturing.

Let's capture whatever this is, then do that, match the age, and then capture the digits there. If we run that, you'll see we get Alice comma 30, Bob comma 25. Incredibly cool that you can do this in the database. String to table, that might look like a CSV to you, but regardless, it might be just an imploded, implode is PHP joined, it might just be a joined string. You might get this from some outside system.

If you got it from PHP, that is an imploded string, and here you can explode it by saying string to table and passing through the delimiter as the second. That is the end of our set generating functions for now.

As we carry on, I might highlight a few more, as we start talking about strings, as we start talking about JSON, we might highlight a few more set generating functions, but now you have a category in which these functions can exist in your brain. They are functions that return not just a single value, but a set of rows and potentially a set of columns as well.