 Okay, everyone. Good morning. Good afternoon. I'm sorry. My name is Bruce Momgen. I'm one of the PostgreSQL team members and employee of Enterprise DB for the past 10 years. James Coleman is the host mentioned was not able to speak today, so they have asked me to take his place. Fortunately, he was speaking about comment table expressions, and I also happened to have a comment table expression presentation. So I was the logical person, so if you did, he come to listen to table expressions, I have you covered. If you came to see James Coleman, not so much. This is a very interesting presentation. I think you'll find it challenging, a little unorthodox in some of the approaches, but it builds up very slowly, and at the end, it's very complicated. So if you're caught aboard like 10 minutes in, don't worry, the rockets are going to kick in at some point, and it's going to kind of take off. This presentation along with 30 other presentations are actually at my website right here. So if you'd like to download these slides even right now, those are on the website. There are recordings of me giving these many of these presentations as well. I do have 90 slides. I do have 50 minutes. So this should be interesting. Again, there is a lot of material here, but again, I don't assume you know what comment table expressions are. I don't assume that you've actually worked with them before. We start very basic, and we build up, and we build up. How is my volume? Is it too loud for people? Is it okay? You're good? Okay, it's not too loud, because I know if I talk really loud, it would be too loud. So I'm trying to be kind of quiet. Maybe I'll, you know what I'll do? Let me lower it a little bit. Maybe I don't make it a little more normal. No, it's too loud. Is that too low? Is that good? Okay, good. So I can talk a little louder maybe and more of my normal voice. So again, we don't assume a lot about comment table expressions. These were added a couple years ago into Postgres, and I think you'll find them to be, to answer, to solve some very interesting problems that you encounter as application developers. It's kind of hard to wrap your head around everything that it can be done with comment table expressions. So again, I'm going to be kind of exhaustive and try and go through it step by step in getting more advanced, more advanced until we get to the end. So as we only have 50 minutes and 90 slides, let's get started. This is the outline of what I want to cover. First, I want to talk about some technical terms, which I think will help to clarify what we're doing. Then I'll talk about the comment table expression syntax. Talk about what recursive comment table expressions sound scary it is. Then we'll go for some examples and finally writeable comment table expressions. So again, some definitions. The difference between imperative and declarative, you see that sometimes academia, but not so much in practical case. We think of job, we think of pearl, we think of SQL, we think of prologue and some of the Erlang and some of the sort of fancy languages. We don't typically classify them, but effectively an imperative language is the language similar to C or pearl. Will you tell the computer what you want to do? You're basically subscribing things in terms of statements, the change program state. This is kind of the programming language that most of us are working in on a day-to-day basis, but there is another kind of language called a declarative language. These are normally higher level languages. SQL happens to be one of them where you don't tell the computer what to do, you tell the computer what you want and the computer then figures out how to get you that result the most efficiently. And frankly, this is one of the reasons the SQL remains popular 40 years in or 40 plus years in because in a lot of ways it removes a burden from the application program. The application program can send the query to the server, the server figures out what indexes to use, figures out how to do the joins, whole bunch of stuff. I have a whole bunch of presentations about that. But the idea of a declarative language is that you tell it what you want and then it figures it out. Here's some examples. This is very simple imperative languages, very easy to understand. The declarative language, this is a little kind of weird. How would you do this looping saying hello, hello over and again in SQL? SQL is a declarative language. There's no way to create a construct, a loop or anything like that. But the bottom line is there are some cases where in fact you want to be imperative, even in SQL. Common table expressions in my mind allow you to do that. It allows you to take yourself out of sort of the declarative box and tell the system I want to do this specific thing and I don't want you to interpret it or whatever. I just want you to do that. I'm going to show you a whole bunch of examples where this is actually very valuable, particularly common table expressions allow this to happen. Again there's a lot of options for imperative programming, client applications, JDBC, LibPQ, Perl, server-side programming, you certainly have a lot of declarative imperative languages and common table expressions, which is another sort of imperative tool in your box. That's why common table expressions are so interesting because they kind of come at SQL from a completely different direction than a lot of other features. This is the syntax for common table expressions. Again, it was added in 8.4, so quite a number of years ago. The syntax always starts with the word with, so if you've ever seen a query that starts with and you're like, I don't see select, they're like, what is that? It's a common table expression. It starts with the word with and then usually the word select is after that, although technically you can do some other things I'll talk about later but I'm giving away the form there. Basically the word with and then some query and then perhaps another query and then eventually usually it ends up with select. That's typically the way these things go. Here's the simplest common table expression that I could think of. Again, we're going to start really slow. Here is that keyword with and the first thing I have to do after that keyword is name the common table expression. That's pretty fundamental to common table expressions because effectively the reason common table expressions are imperative is that effectively you're creating kind of a data source on the fly. It's almost as though you're creating a temporary table. Think of it that way. If you look here I say with and I call the contents of that thing source and then inside that common table expression what do I have? Select one. Super boring but it works. Then once I get out of the common table expression notice it's actually with and a name and as and then I've got some prens and those prens basically border that common table expression to start and stop and then I have outside of the common table expression a with and then notice the use of the keyword source. In fact source is the same. I'm referencing that source that common table expression sort of container that I created above. You can see how it's kind of imperative. Also if you don't believe what I'm doing here you want to run these queries yourself you're welcome to do that. The SQL URL at the bottom there right here. This allows you to actually take the if you just download that go to that URL and just download it you can run this in PSQL and you'll see the exact same slides. In fact when I did this presentation I wrote the SQL and then ran it and then I pasted it into my editor and into my word processor and then made the slides. It was easier to create the SQL first and then and then basically create a frame around it. The other obviously thing that you're noticing is that if there's something in red on the slide that's where you want to look obviously right. There's a lot of text on some of these slides and the red kind of is designed to kind of focus your eyes. So here's a here's another version of the common table expression. The difference here is that we're actually giving the column a name. So as you notice here back here I didn't give it a name so it had this really weird like random name that comes up. Here because I gave the column in the common table expression a name the select star actually supplied the column name. Okay so we're getting a little bigger we're now giving name not only name to our common table expression we're actually naming one of the columns. You can name it this way as well here I didn't name the column inside the common table expression I named it outside the common table expression but again call one call one lines up same thing. Okay we can do more well what we do here we name the column call one inside the common table expression we gave a com call call two outside the common table expression and then when we do the select we call it call three and what do you know call three so just keep in mind there's some layering going on if you override an earlier level it just wipes it out. Okay you can return two columns if you want here I'm returning two columns one two I get one two rows back I can do so so let me ask her any questions so far okay great okay so we're we're eight minutes and we're good okay so let's talk a little bit about union I have to tell you about this because it ends up being used a lot with common table expressions union is a way of sort of pasting two queries together so you take one query you union it with another query and and by default if you use the union without the all clause it gets rid of the duplicates so union without an all gets rid of the duplicates I think it's a really weird default but there's some relational algebra reasons for it which I'm not going to go into even if I could but normally when we're going to use union all with common union with common table expressions we're gonna use that all keyword and you can notice it has not gotten rid of any duplicates there okay so here's where we start to get a little more exciting here I'm creating two common table expressions one is called source one is called source two column names one two I'm sorry values one two on the first cable two three four in the second table I do a select in now here now I have a comma here so here's my first common table expression a comma second common table expression okay and then down here I'm selecting from source then I'm doing union all which doesn't remove the duplicates and then I'm saying give me from source two so I've created two data sources and then I'm querying them separately in separate selects which are connected by union all okay and I have one two three four returned okay so again I again it's it's like we're real you know when you take off in a plane and you're like going down the runway and you're like we're not going fast enough to take off I don't know how many you feel that way but like you're going down and you're like we're probably going like 40 miles an hour I don't understand we're gonna take off and magically you do that's what this presentation is gonna do it starts kind of lumbering down the tarmac a little bit but it eventually takes off and it gets crazy by the end so don't worry so let's do some common table expressions with some real tables here's an example I'm using a system table PG language and I'm joining it to PG roles so this is an entire select statement inside the red and I've created a data source which is the joint of those two and effectively when I do the query down here I get the same results as if I had run that now is this useful no right this is completely fabricated but again it illustrates that we can put queries that even do joins inside of these common table expressions here's what gets a little more interesting again we're not taking off yet but the plane's starting to shake a little bit here we we do a common table expression up here we still call it source it does a join but we're actually doing two different things with the result and this is where we're starting to actually use common table expressions in a way that's actually useful and I've done this actually myself I'm not sure how many of you have also done this but the typical problem you're trying to solve here is you have a query and you want to look at the data in two different ways but you want to return them in the same result set okay and the old way doing that would be to run the query twice you'd have one query here then you know just forget that don't do that so here we say with source we run the query we decide to return all the rows and when you you know all and we then add an additional row on the end it says give me the minimum so effectively what happens here is you got C internal PLP G SQL SQL and then the minimum name of those is land name and as you'll notice there's actually no value over here because I specified null on that side any questions okay so we can join to these common table expressions these sort of fake tables that we create here's a case where again kind of contrived we select a certain number of values from the PG class table we name the columns and we then select and we join the common table expression with another table and then we return the results again probably you'd never want to do this but you kind of get the idea getting a little more interesting we now have the ability to do a case statement inside of a common table expression you're like okay why should I care about this why I'm talking about this because as I said before common table expressions are an imperative give you imperative control of SQL and effectively a case statement although you never maybe thought about this actually acts as an if else clause so it gives you an SQL construct to do if else processing and I'm going to show you a bunch of examples of that as we go forward so here's a refresher here I have selecting a column and I'm actually have a second column that's manufactured that that that column is either going to say positive zero or negative depending on the value of the column okay so I talked about recursive as sort of a way of creating loops in common table expressions and this is the first time we've actually seen the recursive keyword in action okay so I kind of told you about union and union all and you can kind of paste queries together I talked about case statements kind of like acts like an if else thing again I'm kind of getting into an imperative language that has an if statement and and sort of a way of joining things together but I didn't really talk about looping because most languages have some kind of construct to repeatedly do something hopefully not repeat it forever it never finishes but some construct maybe you don't want to finish maybe it's a demon that repeatedly runs things but anyway we're going to assume that you don't want to write an SQL query that just runs forever what you could do in fact this is an example of that there is a special keyword called recursive and if you add that recursive keyword into the common table expression then effectively what happens is that you get it takes the common table expression it runs it it returns the result and then it goes back and it runs it again okay so that's a this is a very simplest example you would never do this in production in fact this is a kind of a case where it runs it returns one row and it just kind of comes down so you say recursive it returns a source but there's no kind of hook here so one row comes out it selects from source in one one row comes out it's very simple okay this is not this is not simple this is where it goes bad okay the difference between this one and this one is that this one doesn't mention source in times inside the common table expression this one does and this is a construct you're going to see over and over in this presentation and frankly over and over in production use the common table expressions here we basically have the same we've recursive keyword the word source we have a select but then we have that union all that thing I talked about earlier and then we have another select which also queries the same common table expression we're generating so this would be bad right this is the ten go to ten kind of command or looping you know while while true kind of command where it just goes on and on so what happens is you return the one but then the rest of the query asks for the one and then you get the one and it returns a one again and then that one comes back in and you return to one again and the only way this actually finished was I I actually set a statement time out so that it would terminate because it would act this would actually never finish so you'd never write something like this when it's important to understand the construct because you are going to be using this kind of construct throughout the entire presentation okay so this is basically the way it works you start with the word recursive up top you have some type of constant to I would say prime the pump okay get it started that's your first value of your loop and then in your union all you have some new value so in this case it just loops forever the one returns the source that source actually is queried here the one goes back up it comes back down it goes back up it comes back down this obviously the two and the three just never finish okay so how do you fix this well this is another example it's also very bad it tip it it technically would say hello over and over again which again is not something what you want to do in production but again in fact it would it would print nothing because it would never finish okay there's one way to fix this and that is to use union without the all because again once you get a duplicate it doesn't rerun so the same thing with the one or hello it doesn't matter it sees it sends the hello the second hello is already the same nothing new is coming out of the query it knows not to run anymore and actually terminates this is not used very much but I just want to give it to you for completeness the way you normally do it is this okay if you don't understand this slide you're gonna have trouble understanding the later slides so I'm actually gonna ask for questions once I'm done the slide the basic issue here is you say with recursive and you have in this case a counter whatever you want to call it you see the value here of one whatever that is and then you union all and here we have a case where the one actually goes up to the counter it comes down to the source because remember we're querying from source this one becomes a two okay so the one comes out the one goes up it comes into here the one plus one is two two goes up two comes down now two becomes three three goes up three comes down three becomes four you got it okay however the reason this works is that where clause again highlight the text in red what I'm saying here is that you can keep this loop going but as soon as the counter is equal or greater than ten we stop okay and this construct you're gonna see over and over again you have a priming query that does a select to get you started okay maybe it'll be a constant maybe it'll be a value from something else whatever okay then you have a union all and then you have another query which also references the same source as the top but you have some kind of limiting condition this limiting condition prevents it from running forever all right any questions about this yes sir okay so the word counter just happens to be a name I gave that column oh if I don't name okay that's a great question if I don't name the column yeah so that wouldn't work because the one would come up but there would be no name to reference it here I think is the problem yeah that would be yeah there would be it would be an anonymous column and then you couldn't you couldn't actually query it here I guess there might be some use for that but you really need this you really almost need a name I hadn't thought of it but yeah you can use it without a name but I would say once you go recursive you kind of got to name it no no no no no you could I think you could but that's a good question you have to have the name up here or could you put the name here I think you could just you could put the name here yeah I think you I don't think you have to have it up there I like it up there because it's clear but I think you don't think you have to other questions yes sir well they are yes sir effectively the way SQL works it returns the data in a non-deterministic order right but because they were generated in that order that's the order we're going to turn them in we could have added an order by down here if we want to override that but normally it will come out in exactly that number whatever now this is in a contrived example again typically if you want to do this you'd run generate series but you get the idea we got it we got to start somewhere this is a good example other questions yes sir that's a good point so the question is if that where claw does that where clause actually running for the value 10 to come down or is it not calling the function at all indeed it is calling the function so if you had a function with side effects in that where clause potentially that could be run yeah now there is some I don't even think we short circuit and clauses so I think it would always run that whole line even if you had a function call in there but again function call with side effects would be the case where it actually would matter that where clause was run and indeed it does other questions yes sir so if you had so the question is if you had a second source here and you reference source in the second source which you can do wouldn't necessarily have the seed value in fact what would happen was you you can actually generate values in source and then those values would feed into source to and source to effectively would run as an independent after source after source right so the first one would pretty much complete before the second one would generate pretty much that's I'm pretty much sure that's how that's the way that we implemented it yeah oh you can't do two recurses in the same query I haven't looked at that I thought you could I mean technically you should be able to but just an ordinary one yeah it would it would run source one and then it would feed into source to yeah other questions