Hi, everyone, I'm a dumb. I totally forgot that there was an Opiechi box promo right here, too That's 2019 2020 Opiechi Platinum sealed box shipped to somebody in this break if you bought at least two teams So there's already alphabetized here in break number nine So Eric got two spots you get one entry Frank got six spots. That's three entries Joey got four spots that counts as two entries The X-Line got two spots that counts as one entry Rob Ryan Scott Had two teams so did Steven B. So that's one entry Wade had four that's two entries and then William Allen yet one. Okay, so 13 names there Let's randomize it eight times name on top gets the box after a two and a six There you go and congrats to there you go Frank. You had a nice break to Frank after eight gets the sealed box You can sell it if you want to or if you choose to open it Let us know if you get anything cool. Jasper's case breaks calm. I'm Joe. I'll see you next time. Bye. Bye


You know, the world in the 21st century faces so many problems which are complicated. But complicated because they involve the whole world, globalization means that the issues are interdependent with each other across borders. So it's a huge enterprise to say, how do you deal with that? How do you bring together and bring forward new ideas that have both the depths to deal with the technical issues and the breadth to deal with all the linkages across the world? The World Economic Forum had thought, right, what we need is global redesign. We need to rethink the way the whole world is organised. There's about 80 global agenda councils. Each one has its own issue and brings together about 15 experts drawn from companies, governments, academia. The Global Agenda Council is a fascinating collection of people, really bright minds. The world's smartest thinkers and experts from around the world, from different regions, different stakeholder groups. And they get together, first by telephone, teleconference, then they come together once a year at the summit. When we come together as a summit, we can grab experts from other parts of the world from other groups and really get a better sense of the issue. The thing is, you can't solve all problems at once and nor can you apply one solution to them all. But as you try to solve each set of problems, you've got to recognise and understand what those other circles look like and how they touch on the issues that you're trying to deal with. Is that interplay between one council and the other on problems which are across the boundaries of the council? That is fascinating and very important. But one of the things that makes this work as a brainstorming exercise is that every agenda council finds its own way of working. And every one of them is actually producing a very different kind of output. Some are producing programs for activism, some are producing very intellectual ideas with footnotes. Each is coming up with a new way of coming at the problem, but they're very different and non-prescriptive. It's really that they're an incubator for the kinds of ideas and debates that global leaders discuss at Davos. We might see this week some sort of template emerge. I'm good to see you. Welcome back to our position.


I'm going to be honest, sometimes I get a kick out of cleaning and organizing. It's just something about it that's very, I don't know, freeing just makes you have more of a clear head, if that makes any sense. So I figured the next best thing to do was to finally organize my shelves in my room because I have quite a pile forming somewhere. Here, of ones that are going to need a home. And for the most part, my shelves, well, the puzzle boxes are kind of just thrown everywhere. Well, let me just show you. So at the very top of my bookshelf is all my Disney puzzles so far. But as you can see, this doesn't have a spot to go in. I have, this one is piled up here. That, that's where the haunted mansion puzzle boxes. And then the rest of my shelf is basically, well, that's my coffee. As you can tell, I collect quite a few things. A lot of these items here are things I've had since I was a child, mainly my Sailor Moon collection here. I'm a huge Sailor Moon fan and I have been since I was a child. So that's my little collection. That's only a few of the things that I currently have. I have my other items stashed somewhere else. I like anime, so I have my Helsinki collection. I am a huge, huge, huge Batman fan. He's my number one superhero. Then down here, I have my collection of Vampire Hunter D novels. My Disney mugs that I got recently that I need to find a home downstairs for. This is actually a candy, a candy holder. I have to move that downstairs. Those are my can holders for soda cans. Over here is my Fear Street collection from the Nandis. That's the rest of my Fear Street collection that I started collecting about a year ago, and I had to stop because the three or four that I am missing are like fifty, sixteen dollars each, which I cannot afford, especially with all these puzzles that I have. And then down here is basically just the rest of the puzzles that kind of just been thrown in place. And more puzzles. Who remembers these thermoses in your lunchboxes? This is super 90s, huh? That's still my original one from when I was a child. And then those three, those are from the last puzzle hall you saw for the autumn one. And then we have Snow White here, which I never did put back on the shelf because really it doesn't fit anywhere, at least not neatly. So what I think I'm going to do is I'm going to take these two shelves of books, move them down one. I would kind of like to have my puzzles in these two shelves here instead of one up there and then one all the way on the bottom. I'm going to move this shelf up here, move that shelf down here, and then these two all the way down and move that one up. I'm probably making it sound more confusing than what it is, but we'll do that together. And then I'm trying to find some other spots in my room. And I found down here, I could probably that'll be a good spot to store some extra puzzles in there. I also have an area here of a bookshelf of things. We've been kind of like emptying out over time. So I've gotten some space here. These puzzles, to be honest, look good on this shelf. I think I'm going to leave these here. These were from the Dollar General Hall, and that's from the Hershey Park Hall. And there is another shelf over here that has kind of like the small boxes. And you'll recognize some of them from my, what was it, Dollar General Hall. And I have a Dollar Tree Hall. But I think I'm going to put like the little baby puzzle boxes in that shelf. So I have, I do have some space here. This is good. This I think will be kind of like the non Disney shelf. So probably could use a dusting too, to be honest. Embarrassing. This is real life here. So, yeah, let's get started with moving these shelves down and then start organizing it all. All right, let's get started. Through it too. Might as well, right? Kind of gets neglected.   It's like, you almost feel like, what's the point? I dust it and then in a few days dusts his back on it again. It's obnoxious. That has to be done, right? They keep me company. Speaking of company, thank you to all of those who've subscribed so far. It's been really awesome seeing your comments. And it's been really nice to connect with people who care about puzzles as much as I do. So it's, it's cool. So thank you for the company. Let's get back to this. Things here, we have Snape, Sailor Moon. Everything looks good. Now I need to figure out how I'm going to go about organizing all the Disney puzzles because I think I'm going to do Disney on these two shelves. And I'm pretty sure I have enough to fill two shelves of Disney. So let's get started with that. Suggestions how to organize these boxes. If so, let me know because I'm, I'm struggling a little bit. I don't think this is, this doesn't look too bad. I kind of have like those green, those green tops in one spot. That, that's much thicker. Let's put that one here. There we go. That looks better. Kind of like all my Disney park puzzles here. The big Chaco boxes. I have the puzzle sets. I don't know. I think that looks all right. What do you think? Would you organize it a little differently? I have all the Disney park exclusives. I have the Chaco ones that you kind of find out Walmart target in those kind of places. I got the big boxes down here and the sets. I couldn't really squeeze this one in there, which is super annoying. I'm not going to risk squeezing that in there. That really does bother me though. Maybe if we did it like that on the bottom, that doesn't look too bad. I don't think. All right. So we have all those there. Then we have this bottom bit here. I have my two Ravens burger. That's another signature puzzle that I have. Little Lilo and Stitch. I put the thermos there for now and the rest of the big boxes. And then my Fear Street and Vampire Hunter Decollection is towards the bottom. Looks pretty darn good. So let's see what we can do down here. So we're going to leave those small puzzle boxes in this shelf up here. Let's work on this shelf here and see how much we can fit of our non-Disney puzzles. I have one puzzle left. I guess it's going to have to go with the mini puzzles for now. There we go. That's all we can really do for now. That's not bad. It's got a home now. Now I just realized I have all those shipping boxes filled with more puzzles in them. That's pretty much all my space. Oh, no, wait a minute. I have the space down here as well that I can utilize if needs be. Well, I'm really happy how that turned out to be honest. The bigger challenge is trying to keep it this way and not just throwing puzzle boxes that I get into any spot that I find and just take the few minutes to just organize it. So let me know what you think about this setup here. If you have a better idea or some other organizing hacks that you'd like to share, please let me know because I could use all the help I can get sometimes. Sometimes I get a kick out of cleaning and organizing. It's just something about it that's very, I don't know, freeing just makes you have more of a clear head if that makes any sense. I don't know. I deal with anxiety a lot and if I have too much mess around me, it just totally messes me up. Are you guys the same? Like you just, you don't feel like cleaning, but if you have a mess, then it drives you just as crazy as cleaning would. Am I making sense here? But yeah, a cleaner environment definitely helps my mind ease a bit better, especially after you come home from work. I work part-time, but I work like every other day. So I don't really get like a two-day weekend. So I'm busy, you know, between that, you know, cooking, cleaning, household stuff. I'm a mom. I'm a wife. Sometimes you just got to take a moment to just clear your surroundings, organize a bit to help clear your mind. And, you know, for me, puzzles is another way to help me clear my mind. And it's why I do them. It's the time I take for myself to kind of just take a step back, sit down, grab a cup of coffee, put some music on, and just, you know, get in the mood of relaxation and just, you know, having fun with the good puzzle. And you know me. You know how I am about puzzle images. If I'm so into it, you know, I'm going to like jump into the image. That's like my thing. So it's kind of like an escape in a way. Do you guys do that as well? Or am I just like the only crazy one on this planet that does things like that? And again, thank you to all those who've joined my channel. It's really exciting and motivating. It's encouraging to know that there is others out there who enjoy puzzles as much as I do. It's also been really nice seeing your comments and just writing back to you. I feel like I got pen pals or something. You know, I'm an introvert. So I don't really get out much aside from like work and doing everyday family household things. So I'm seeing now that joining YouTube has made it a much easier way for me to connect with others who share the same interest as I do. And it's nice to know that people are watching you.  So it's not, it's not an easy task to accomplish. It's no small feat. One thing I've learned, you know, in the last few years is you really have to take time for yourself.  I'm sure puzzles, you know, doing puzzles as a YouTube channel isn't like the very first thing most people would think of starting YouTube with. Too many. It's not the most riveting thing in the world, but to some of us, you know, it's, it's everything. It's relaxing. It's fun. It's therapeutic.   And that's just kind of why I do it. So yeah, thank you for joining me on this YouTube journey and for really helping me continue to move forward with it and do new things and, you know, learn more. All right, guys, that's it for this video. I'll see you next time. Bye.


In classroom conversations, I will be the mean teacher who asks the mean question. I will be saying things that many minority people receive on their daily life and ask what students for an explanation. For example, I'll be saying things like, okay, so tell me what you can say to me if I say this to you. Poor people in this country, they do it to themselves. They are poor because they are lazy. Don't you think that this is a pretty nice country? If you try, you have a chance of succeeding. Then students will give me an explanation and they will give me clarification so that this sort of microaggressions examples or multiple examples will go on along class from teaching along our discussion. In terms of academic projects, I do in my advanced intercultural comm class where at the very beginning of the quarter when students are not exposed to whiteness studies when they are not exposed to the journal articles, I would give them a set of questions. What do you think whiteness is? What do white people do? So that they have to come prepared thinking about those questions and they are in small groups recording their own conversations. So then they have the conversations, they record the conversation, they transcribe the conversations. So then students will be interested in finding out like, maybe how racist I am. Well, you said you were not right, but look at the discourse. So then I teach them techniques of discourse analysis. What is sate plus what is unsate. So then they can learn about how they have always lived their life as an oppressor maybe. I let students know upfront at the very beginning quarter, at the very beginning of the quarter that this is going to be a very uncomfortable experience and especially for many of you, many of you who live in the privileged lights. However, in order to engage in that sort of discussion and conversation, we need to be able to hear other people's out. We need to be able to lower our gut and to say that I am wrong. I expect my students to be able to take responsibility. Whenever students say that, oh, that was my ancestor, it has nothing to do with me. I will have other students in the room that call out that action. So then in many things that we do and here in the classroom is supported by evidence. How do you derive at that conclusion? We also have very derived readings. Most of them are published and written by scholars of color. And what people say they are published in the outlets that are not mainstream. For example, I have readings where scholars talk about how scholars of color are never accepted in academia. You are always expected to assimilate. Assimilate means act white. And you've got to write topics that white people want to hear. So then by giving them all this example, it sort of give them the invisible tool that it will never be the same situation that I'm going to face. But when I see this happening in an organizational policy, I think that many daily life examples I draw give them the motivation to say that if a tiny Asian woman like Dr. Lee can say that, you can do it.


and welcome to our rink and we're ready to go.  Broke it up with the line by Rivera. Now Nick Foster will bring it in. It plays off sides. Jaden Blackburn at the dot outside the line. They'll win it. Sloan throws it in deep. Blackburn on the chase. They're taken over by the Colts. There's Foster battles on the Colts. Blackburn comes out of it. The Sloan behind the net and the Colts take over the puck and grind it out. Now we intercept it. Jaden loses his edge, it goes down. Colts will take over. Throw a shot on Hanscom.  A first face off for Hanscom's right. Koji's there, they'll win it. That's Alexis. Brits up along the boards, kept in by the Colts at the line. And a whistle will take it down, a little hand pass on the inside. The attack's on forever. Koji, a cheater at the dock. One by the Colts. That's Alexis. Take the man of the puck, we'll go all the way down to the other end. Picing's waved off, Nick Paul's there. Takes the hit, gives it up to Master Alexis. Over for Brits, can't connect. Back to center. Koji intercepts a pass. They'll skate it in. Quick shot above the glass. Colts will ride it out. Now up to play by Foster. Back to the line. Throw him back in deep. Koji's sheath is there, he'll wrap it around. Looking for Brits. Can't connect, Nick Foster down behind the net. Up and over the boards for Agio Rivera. Now up for Brits. Now Koji loses the handle. And they'll call that off sides. Face off outside the line. One by the Colts. They'll skate it all down low. Carter Beckwith's there. They'll skate around Hanscombe. Leaves it up for Evan Smith. Looking for Frontier on the fly, can't connect. They're cutting on it, going down deep. Now Frontier, centering pass in, and Smith. Just a little too deep in front. They'll take the hit and go down. And the Colts will skate it in. Put it by Foster. Up along the boards. And I say, face off one by the Hurricanes. Still not out, kept in by the Colts, thrown in deep. Nassau-Alexis is there in the corner. Colts up along the boards, kept in by the Colts. Locked out front by Nick Paul. Now up for Chris Foster. He's ridden off. Tough angled shot by Slovin. Goes off the back of the net. Stolen away by the Colts. Paul takes it down hard. It's like an upcoming penalty on the Colts. Got 11-21 in the first period. It'll be a hold on 11. Face off one by the Colts, and they'll throw it out. On the power play for a quick two here. Back for it, Blackburn. Leaves it for Foster. Foster go by his band. Now at center, gains a line. Leaves it for Koji. Shot in, off to a stick of Smith to the corner. Foster with it. Back to the line to Koji. Straight shot in goes Y. Koji now at the other line. To Foster, can't get much on it. And it'll wrap in. Goes by Vazina. Amherst goes up, 1-0 at 10-38. Power play goal. So no goal at 10-38. Arkane's will skate it in. One minute on the power play. Chris Foster, cross corner to Nick. His shot in's deflected out front. Edmund Smith's there, wraps it around the boards. Look at for Chris Foster, kept in by Koji at the line. Bounce and puck. Screenshot in, Vazina's. There were the save. Blackburn, now Koji. Leaves it for Massive Alexis. Shot in, just off of Chris Foster's stick. 20 seconds on the power play. Back to the line, stopped out front. Blocked with the back of the net. Tough angle shot out front. Look at for Smith, kept in at the line. And Koji, back to full strength. Rashida in the corner. Alvarvera, bounce and puck. Both have it. And they'll play it out. We got a one on two developing. Can't connect. Massive Alexis just rode them off to just enough to the corner. Shot goes across the corner. Vera on the boards. Close come up. Shot all the way through again. Vera's there. Close it off the glass for Peritz. He's on the fly. Kicks save, Vazina. And the Colts will skate it out. Carter Becker is back. He'll throw it on the back of the boards. He's got Jared Cutting. He'll throw a flicker to pass all the way back out to center. And Vera steals the puck. Like a little holding there. Now Addo's in the corner. And they'll call the penalty on Joe Frenier. It's 7-43. And the face off will come all the way down to Hanson's left. Screenshot all the way across goes through. Beckwith loses the edge. Foster has it wrapped around the boards. Foster stops it out front, back to the line. Wide shot. Foster along the boards. Can't connect with Blackburn on the fly. Chris Foster. He'll throw it all the way out. Blackburn's there by himself. Behind the net. Oh, we're back to Chris at center. Chris will throw back in. And Mastral Exis throws it back all the way down to the corner. Mazzini will handle it. Blackburn on the forecheck. Back to centers. Blackburn leaves it off for Nick Paul. Paul throws it back in. Forechecking by Blackburn makes him recycle back to behind the net. Coastal skated out. Deflected off of Mastral Exis to the corner. Back around the boards. Shot in to fleck it out front. Colts on the power play. And he'll throw it out. 20 seconds now.  They'll skate it out and that'll do it for the Colts power play. Look at how the whistle brings it all the way. The face off goes down to the right of Azina. Koji Ashida's there. They'll win it back for Nick Foster. And Glovesave Kyle Vazina. 5'30 to go in the first. Tied up to 0-0 here at our rink. Koji takes the face off and the Colts will win that one. Kept in at line by Beckwith. Shoot is there. That gets deflected up and out.  Blocked out front. The bear has it behind the net. Colts steal. And they'll skate it out. That'll be a penalty on Brandon 459.  They'll throw it all the way down on the other end. Leaves it for Nick Paul. And Paul goes down on the heat. Glovesave Azina. Got Chris Foster, Nick Foster, Nick Paul. Shot in, deflected just wide. Paul takes the body. Puck goes to the other end. Nassau-Alexis is in the area clear. Colts will just throw it back in. Nassau-Alexis is back. Up along the boards, now out. Back to center, Blackburn. Leaves for Chris Foster. Foster in. He's ridden off for the corner. And tough angles shot. We tied up by Kyle Vazina. 341. Just under a minute to go on the power play for the Chickabee Comp Colts. One by Chris Scott in, by Foster, tied up again. Koji's there. One by the Colts. 30 seconds on the power play. Kept in by the Hurricanes. And the Colts will regroup. She'll come off with some speed. And Carter Beckwith blocked a shot. And the rebound goes all the way back down to Vazina. Five seconds. Power plays up. Shot goes above Hanscom. Nick Foster's there in the corner. Back to the line. Shot in, deflected off of Rivera. Tapped out by Chris Foster. Back to the line. Colts will skate it in. Tough angles, shot goes by Hanscom. Little Chickabee Comp will go up one nothing on the Hurricanes at 220. An interference call on the captain, number six, off to Face-off. And we'll see if they can get something going on this power play. Chris Foster. Bouncing puck goes all the way down the other end. Nick Foster goes after it. High stick on the Colts. It'll be five on three. Colts win the Face-off, taken over by Devin Smith. Kept in at the line by Koji. Into Blackburn. Screenshot off Evan Smith. Blackburn, now to Koji at the line. Kicked save, Zina. Face-off won by the Colts, he'll throw it back in low. Blackburn over to Foster. This scored the goal up for Koji. He'll skate it in, go deep. Into Evan Smith, rebound, Blackburn tied up by Zina. Hours goal scored by Mark Foster, assisted by Koji. Face-off will go to the left of Kyle. At the dot is Blackburn. Pull in to Face-off, back for Foster. Give him a whole shot in. Blocker save, Zina. Back around the boards. Foster gives a hit, puck comes out of the line. Blackburn, plays it in, looking for help. 38 seconds.  Awesome pop up for Koji, Brandon Pritz. Back to the Face-off, won by Chickabee Cop, thrown all the way down onto Hans, gonna be able to kick it aside. Pritz, can't clear. Master Alexis in the corner. That's how Koji takes the hit. And that'll do it for the first period. Amherst 3, Chickabee Cop, won. Back to second period play. Face-off won by Hurricanes. Blackburn will skate it in. Back to the line, Foster, shot, block out front. Stop at center by Blackburn. He's ridden off the play by the Colts. They'll throw it out. Back for it, Nick Foster. He'll throw it back in, up along the boards. It'll be goal, Slovan at 14-20, second period. Back to the center dot. Koji, a sheet is there. He's got Geo and Brandon Pritz. Nick Paul punches down, takes the hit, puck goes deep. Kicked aside by Hanskin. Passed on by Master Alexis. He's thrown out. Shipped away by Rivera. Deep up along the boards, kept in at the line, Master Alexis. Throws it in, stopped out front by the Colts. Koji along the boards. They'll come out of the boards with it. Leaves it for Nick Paul. He'll throw it down deep. Brandon Pritz is there. Now Rivera is centering. Turn around by Koji, stopped out front. And they're clear to center. Puck goes all the way in deep. Centering pass all the way through. Brandon Pritz off the glass, and that'll clear it. Rivera intercepts again. Looking for help. And the Colts will take it over. Back to center, it goes. Stopped the line by Nick Foster. Looking for having snipped. The play will be off sides. The face off will be outside the line. Take it by Joe Frenier. They'll win it, drive it in. Evan Smith can't handle it. The Colts will ride it out. Four on one, and not cleared. Throwing back in by the Colts. Up for Evan Smith. They'll skate it out. Gains the line. They'll go deep. He's written behind the boards. Pucks comes loose. Back to the line. Foster just off the club of the Xena. Back in the corner of the ball it goes. Comes out to the boards. Stopped by Master Alexis. He'll skate it in. As Amherst makes a change. He's written off the play out front. Now he'll skate it out. Good beans, got some speed. He's going to get saved. Pass is intercepted by the Colts. Slowed and down. Nick Paul now. His pass intercepted. And the play is off sides. Face off one by the Colts. They'll throw it in. Nick Foster's there. And Nick Foster. They'll ride it in. Shot gets deflected over the top. Over the Koji. Cheetah comes away with it. Throws it in. Picks saved. By Vasina now written off. Colts will skate it in. Throw it on. Gloves saved. Hands come. Will throw it out. Can stick by. Keeps the play in. Hurt takes the bodies. Centering pass in. Take it away by the Colts. Step in by Master Alexis at the line. Deflected out front. Pass in front. Colts will skate it out. Foster takes the puck. Now Sloven with it. Up for Koji. The Blackburn. Kept in at the line by Ishida. Face off outside the line. At the Dots Blackburn with Chris Foster. Adidas Eye Sloven. Master Alexis. And Nick Foster. Jaded in the corner. Now Chris Foster. Back the line. Master Alexis. Shot in. Reflected just wide. Screenshot all the way across the crease. Kept in. Now thrown out by the Colts. Back forward Foster. Tough angled shot. Now tied up by Hanscom. Amherst will make a full change. Evan Smith's line. Joe Frenier at the Dots.  Shot across goes through. George Alamos up to Frenier. Stopped at the line. Alamos will tip it back in deep. Along the boards. Now out. Colts will skate it in. Along the boards. Stop at the Dots. Up for Smith. Foster loses control of Puck. Eight minutes to go. Second period. Foster. Screenshot in front. Stopped by Beckwith. Kicked aside by Hanscom. Back the line now up. Smith will. Draw the penalty. 739. Going the second period. Number 21. In the box for two. And Amherst will go on the power play for two. Five on four.  Shot in. Rebound. Blue self front. And a whistle. See you don't have another face off to the right. And Blackburn in the dock. Rashida and Rivera. Back to the line. Goes by Machel Alexis. A little reset. Up for Blackburn. Back to Rashida. Shot on. Gloves save. Just over seven minutes to go. Second period. Amherst up. Four to one. Over Chickabee Cop. Back at the dock. One by the Hurricanes. Goji will wheel. Machel Alexis pitches down. Into the corner. Into Rivera. Can't get anything on the shot. Blackburn into the corner. Back to the line. Rebound. Just tied up by Vazina. 644.  One by the Hurricanes. Kipped in low. By Rashida. Bounces along. And now cultural clear. Back is it. Is Nick Foster. Up for Koji Rashida. Will gain the line. Nick Foster has it. Pitches down low. Blackburn. Handhandle pass. Back behind the net. Koji back to the line. Machel Alexis back to Koji. Blackburn's got Edmund Smith out front. Bouncing puck out front is cleared. Shots to stop out front. By Foster. Now Koji in the corner. Handle these up. Blackburn on the fly. Harris will go up. Five to one. With 529 to go. Second period. Back to the center dock. Rashida wins it back. Carter Beckwick. Nick Paul and Koji gets ridden off the play. And the Colts will clear it out. Nick Paul. Just over five because the dots are here.  Play will be off sides. And the face off will be outside the line. Just over five minutes to go. Second period. Hammers up. Five to one.  Kick to the corner. By Hanscombe. Back the line. Shot in. Kick to side by Hanscombe. Nick Paul's in the corner. He'll throw it around the back of the net. Foster off the stick of Hanscombe. Clear to center. Evans Smith stick. Joe Furnier will take it in deep on the corner. Jared Cutty. And Furnier. So Lexus Colts it in deep. Master Lexus. Mike Chickabee Cop. Puss it by Hanscombe at 353. Furnier in the box for two. Face off one by the Colts. They're on the power play. Shot in. Kick to side. Tied up by Hanscombe. They should get up. They should get up. Face off one by the Hurricanes. And now out. Take it away by Blackburn. Takes the save. It's under 330 to go second period. It's five to two. At the dots. Blackburn. One by the Hurricanes. They'll keep it in. Foster Cainhill. Pass across and fosters out. All the way back down. Chris Foster. And he'll play it out. Throws it back in deep. One minute on the power play. Pass intercepted by Foster. Foster in deck with back. Hooked away by Foster in the corner. Centering pass. Taken over by Nick Foster. He'll throw it out. Amherst makes a change. Back it's centered. Colts will skate it in. And thrown out. Fifteen seconds. That goes all the way back down to Bazzina. He has to play it. Pressured by Rivera. Skated out now by the Colts to center. And the power plays up for the Colts. Chickabee caught. That 145. Chickabee will get another one back. Now five to three. Sheda at the dot. He'll win it and take it. Skated in. Got Rivera. Good enough by Rivera to center. Offside.  Face off outside of the line. At 130. One by Sheda. And he'll skate it in. Foster. All the way to the boards. Stopped by Brandon Fritz at center. Gloves save. Hands come. Now we got one minute. One minute. Second period. Amherst up five to three. Face off one by Hurricanes. He'll play it up and out. Chris Foster's there. Leveled by Nick Foster. That was a legal hit. He's up. 42 seconds. Second period. Face off outside the line. Will be taken by Blackburn. Back for Carter Beckwith. They'll throw it in deep. Chris Foster. Eli Sloven throws it in. Tied up by Vazina. 27 seconds left. Second period. Face off to the right of Kyle. And the dot goes. Gated Blackburn. Colts will win this one. They'll skate it out. 20 seconds. Game of the line. Chris Foster. Eli Sloven throws it in. Tied up by Vazina. 27 seconds left. Game of the line. Carter Beckwith takes a puck. We're into the corner. Now Blackburn gives a hand. 10 seconds. Seven seconds. Blackburn. Seconds to go. Up six to three. It's under five to go. Back center of the dot. Amherst wins it. Do it for second period. Amherst six. Three. Third period action here at Orink. Amherst six. Chickabee comp three. A little strum at center. Now Sloven takes over. Shot goes wide. Now out. And Nick Foster will throw it back in. Back at center. Nick Foster. Sloven. Plays just off sides. Outside the dot. Colts. Now Nick Paul has it. Stolen by the Colts. Turning pass in. Just chipped away by Foster. Balancing puck now out. Up for Jayden Blackburn on the fly. He's got Sloven on the wing. Shot just wide. Vazina. Balancing puck back to the corner. All the way back down for Beckwith. Beckwith all the way around for Rivera on the boards. Shot goes wide at Hanson. Now cleared out. Now Rivera. Now Koji. Wheels around the net. Foster's drive. Stopped out front. Kept in by Yoshida. The line. Now Basso with a stick. Now back for Carter Beckwith. And he'll skate it in. And take it away by the Colts. Scrum at center. The Colts will reset. Intercepted by Blackburn. Chipped away by Yoshida. To the corner. Now Nick Foster. Up along the boards. And now out. Chris Foster. Now Master Lexus. Basso's just out. Throwing back in by the Colts. Foster and Blackburn on the fly. Now Rivera. He'll come off the boards with it. Rivera. Blackburn. Foster. I've stopped out front. Rivera in the corner with it. He circles. Centering pass. Tied up out front. Gloved by Wazina. Blackburn at the top. On by the Colts. Take it away by Chris Foster. Tough angled shot. Rebound Sloven goes wide. Back to the line. Master Lexus shot in. Goes wide. Along the boards. And now out. Tree back by Nick Paul. Bouncing puck. Taking over by Blackburn. He'll wheel in. Centering pass is tied up. By Kyle Wazina.  Of a face off at 11-09. Colts will win this one. Cutting in on the four check. Puck comes out to center. Shot stopped out front. Hooked away by Evan Smith. Now front here. Smith. Cutting. Had to clear the zone. Couldn't make a play. Back to center. Evan Smith. Over to cutting in the corner. Centering pass in. Bounces up over the top on that. Evan centering pass. Bouncing puck out front. And the Colts will skate it out. Kicked aside by Hanscom. On the boards. Kicked aside again by Hanscom. Can't tie it up. Goes back to the corner. Along the boards. Nick Foster's there. Up the post. Peritz up for Jared Cutting. His shot.  At 9-40. In the third. Hammer six. Trick or Becom three. Out comes Kody Oshida's line with Brandon Peritz. And Gio Rivera. Back on D's master Alexis. And they'll skate by him. Now Nick Paul. Up for Peritz. Take it away by the Colts. Centering pass goes all the way through. Off the boards. Peritz is there. Now Koji to the corner. Back behind the net. Stopped at the line. And now skated out by the Colts. And thrown in. Over for Rivera along to Koji. Centering pass in Rivera. Can't make it work. Shot stopped out front. Koji takes the hit. Puck stays in the zone. Over for Rivera. We'll have another face off at 8-13. Third period. Back at the dock. Colts will win this face off. And Chris Foster stops him from going all along the boards. Carter Beckwith at the line. Will fill it up. And down all the way. The end. And out. Thrown back in. Gloved by Vizina. Burning as fs. Gloves save. Vizina. 7-36 to go. Third period. Armour stops 6-3. Blackburn. Run by the Colts. Can't get by Beckwith. Now Chris Foster. Now Nick Foster. Up for Blackburn. He's right into the corner. Now Chris Foster. Backhander. Won't go. Goli's lost his stick for the minute. And that'll be icing. We're right at 7 minutes.  Who's 7 minutes to go? And this is the 7... Third period. Should be able to win this one. Nick Paul can't keep it in at the line. Paul can't keep it in at the line. All the way back to the end boards with it. Quick stop. Going to the boards for Rivera. Now Koji. Now Rivera. Now Nick Paul in the corner. Vasco's all the way to the far corner. Now Master Alexis. Now Koji has it along the boards. For Pritz. He'll tap it by him. Leaves it for Koji. Centering pass. Cross. Rivera. Won't get a stick on it. He'll go down. Loses an edge. And now out. In by Nick Paul. Alexis. The play will be off sides. 6 minutes to go. Third period. Face off at the far dot. Outside the line. On back by the Colts. Pressured by Smith. Now Alamos. Now ridden out by the Colts. Beck was there. Screenshot in. Tied up by Hanscombe.  Joe Frenier is there. He'll take the puck. And Colts will set the pass. Centering pass goes all the way through. We're now out. Stick. Sticks. Center pass goes all the way through. We're now out. Sticked away by Hanscombe. Off for Joe Frenier. Off his stick. Back to center. Nick Foster's stick. Hanscombe leaves it for back over for Beckwith. Now Evan Smith plays it out. Back for it. Nick Foster. His pass. Stop. Just by Evan Smith's stick. Frenier. Evan Smith went down kind of funny there. Pass intercepted. Mess Alexis. Left it for Blackburn. Now sloven along the boards. Kept in the line by Nick Paul. A little chippy now here at the third. Just turned to five minutes to go.  A little cycle in. Shoulder. Throwing all the way into Vizina. Supposed to have right Blackburn's air. From behind the net. Kicked away. Bouncing puck out front. Tied up. By Vizina. Four minutes to go. Third period. And we're still up six to three. And we're still making a defensive swap on comes Carter Beckwith and Nick Foster. Crispy. Nick Foster. Chris Foster. Battles on the line. Throws it back in. The whistle's above. We'll go back to the center dot.  Just under four minutes to go. So now they say no. Should buy sloven. Now Chris Foster. In the corner. Comes up along the boards. Stopped at center by Nick Foster. Blackburn will take over the puck. Skip over a check. Nick Foster. Blackburn sloven can't keep it in. Blackburn back to center. He wheels. Throws it in deep. And that line will make a change. Comes Rivera. Britz breaks it up right away off them. Just poked away. Back to center. Britz plays it in deep. Thrown back in by Britz. And we'll get a hook on Amherst. 214 to go. And Brandon will take a seat for two.  Come back down to Hanscom's left. We taken by Jane and Blackburn. One back by the Colts. Shot's ridden off to the corner. Blackburn takes it away. His shot gloves say Visina. Two minutes to go. Third period.  One by Blackburn. Can't control the puck. Steals it away. All the way back to the blue line. Nassar Alexis throws it back in deep. Nassar Alexis pitches down low. Backhander. Stopped out front. Colts lose an edge. Back to center. Stopped out front by Foster. Now good hind. He'll play it out. Foster. He's taken down by a slash at 112. We'll have four on four hockey for about a minute.  The puck comes all the way back down to Nick Foster. Up for Ashida. He's got Rivera. His shot goes wide. Hooked down low by Rich. By the Colts. Shot goes wide. 30 seconds to go. Rivera skates it in. Jared Cutting. Backhander. Now tied up. 15 seconds. Left in the game. Edmund Smith will take it at the dock. 10 seconds. Rivera shot blocked out front. Kept in five seconds. And that'll do it. Two regular season games left. One against South Hadley. One against Aguam. And then we're on to the playoffs. This is goodbye and good night. One more rink.


Welcome to today's Postgres Conference webinar, Functions, Use and Misuse. We're joined by Henrietta Dombrovskaya, Director of Analytics at Brokerx, who will discuss how functions are executed and are using functions effects performance, how to use functions with user-defined types, the advantage and disadvantages of using functions in OPT and OLAP environments, and how to use functions with Dynamics SQL. My name is Lindsay Hooper. I'm one of the Postgres Conference organizers, and I'll be your moderator for this webinar. A little bit about your speaker. So Hetty is a database researcher and developer with over 35 years of academic and industrial experience. She holds a PhD in computer science from the University of St. Petersburg in Russia, and at present she's the Director of Data Analytics at Brokerx, a local organizer of the Chicago PostgresQL user group, an active community member and a frequent speaker at Postgres conferences, an author of PostgresQL query optimization book, and many, many other titles. So welcome to you, Hetty. So with that, I'm going to hand it off. Take it away. All right. Thank you, Lindsay. Hello, everybody. Thank you so much for attending my presentation. As Lindsay just noted, everything is recorded, so I really appreciate people taking time and attending in person because everybody needs audience. You know, even silent audience is still better than that. So today's topic is functions and procedures, use and misuse. And indeed, functions and storage procedures are in some sense, I think, most controversial objects in PostgresQL because I think at least 50% of the time they are used like in the wrong place and not used in the right place. So as it was mentioned, the examples which I will use coming from the book Postgres query optimization, so just 10 seconds of self-promotion. Book is still there available in Amazon, in a paperback, and in electronic. And all the examples are available on the open source Postgres Air database. So you should have this link and like if you care, you can just, you know, get this code from your screens. So this is like full-size database. You can download it and use for all sorts of different things. You can test pretty much everything on this database. So again, the examples will be based on the tables from this database, and I will like mention briefly what they are, but hopefully most examples will be like self-explanatory. So we will start with functions and how we're using it. So why functions? Like as I said, functions are most underused and misused objects in PostgresQL, from my perspective. So why? Because everybody started from some programming language and everybody knows how to write functions in programming language. So does it help to write Postgres functions? Actually, it does not help to write Postgres functions. So let's take a closer look at why and how Postgres functions are different from functions in programming languages. So first of all, there are two types of functions. First are internal functions, which are actually pretty much like your functions in C or Java whatever you want. You can do like mathematical functions, string functions, time functions, so those are kind of something which are really familiar. And then there are user defined functions, which can be written in query languages like functions written in SQL, there can be C functions, or like on C or C like languages. And there are other functions, which can be written on procedural languages. For example, like on PL SQL, you can do Python functions and many other functions. So in today's presentation, we'll be talking solely about functions, which are written in Postgres programming procedural languages, PLPG, SQL. So all the functions, which are written in PLPG, SQL can include any SQL operator, any control structures, if case loops, et cetera, and it can include calls to other functions. So for start, let's look at one example of how to create functions. To create function, we use create function operator. So what we have here, that is the name of the function. So the name of the function is texting numeric. Then it has parameter. The parameter is called input text and it's of type text. Then they're saying it returns integer and it is written on PLPG SQL language. And all the rest is the function body, so starts from body and from body. And that is like a very simple thing, which is basically converting text to integer. And the first question you might ask, why we need to create a separate function for this simple operation? Doesn't Postgres have ways to convert text to numeric? Absolutely. And actually several ways to convert text to numeric. So you can do past, you can do like convert to numeric, you can convert to number, like all sorts of things. But what happens if the text that you pass is not something which is convertible to number? So if it's not like one or 100 or whatever, what it's like A1. So what will happen? All of this system supplied conversion function will arrow, it will generate Postgres arrow. And then the whole SQL where you are applying this function will collapse and you do not want it. So you want to do something in exception. So what to do when the parameter which is past does not represent numeric. So in this function, we said, okay, do nothing, return null. So this allows us to operate on some strings and if it converts to integer grade, if nothing return null. Actually, that might be not always a good idea to use this type of functions, but at least that's the purpose. And we will see in a little bit why it might not be always a good idea. So now really quickly, because that is everything you can see in documentation. So I will go through the next couple of slides really quickly. So function might have named parameters or position parameters. So we can define it by name as you have input text or we can just have parameter one, parameter two, et cetera. Parameters might be specified as in, out or in, out, although most of the time we use in parameters without out. And then we just return function, result of the function like what it returns. Also function, there is such a thing as function overload, which means you can define the function with the same name and with different types of different sets of parameters, different number of parameters, different part of parameters. So you may call the same function like here again, we are using for this air database in the function num passengers, calculates the number of passengers. It can be on the flight ID. So we can pass flight ID, return number of passengers on the flight. We can also return number of passengers, which we departed from this specific airport on the specific date. Or we can do it by the flight, flight number, which is text field. So everything here will be actually fine, except off, we cannot change the return type. So when we try to change return type, so we had integer, integer, integer, and then we said ops, numeric and this will return an error because all the functions which we use for overload, if we have the same name parameters might be different, but it should return the same type. So that's like all the exception. Another thing which is worth mentioning, it's nested all the quoting because that's absolutely wonderful for the switcher as quite often you need to have string within string, like example here, you want to pass an error message and you need to have the text error, you need to have quote inside. For example, here, a record can be updated. So you need hands with quote. So if you do not have a special notation for handling this, you always need to double this quote, like the triple this quote and whatever. So postgres allows nested dollar quoting. So every double dollar sign means, might mean the beginning of the string and then matching the double dollar sign means the end of the string. And also you can put any identifier in between. So if you look at the function body, function body is also a text. So it's passed to the create function as a text. So we have here dollar function dollar. That's like the end of the text of the function. And the beginning was also function. And inside you can have multiple other dollar quarters and they all will be nested properly. So you can have string within string within string. It's actually extremely helpful, one of the best features for this. So this is said, what we just mentioned. So the body of the function is passed when function is created as the string, which means it can be pretty much anything. And all the very, very basic syntax checks are performed when function is created. So this has been said, when you create function, like say you did it in Oracle, for example, or if I can sign base, most of the time assume, OK, create function, function created successfully. That means function works. So with Postgres, it means virtually nothing. So the only thing that means that you have the right matching the number of if, then, and if, or like case and case. So all the very basic checks, there might be lots of errors which are not identified when function is created. Again, that is an example from Postgres Air Database. So we create this function number of passengers departed from this airport on this departure date. We run create, it created successfully, right? Now we want to execute it. So now we're trying to execute it and create return successfully. Great. We do execute, select number of passengers departed from Ahara on UI7. What we're getting? We're getting an error message. Column airport code does not exist. So what? Yeah, because actually we set airport code in the function, but actually the correct column name was departure airport. So Postgres didn't even check whether we reference correct columns for this table. Then, OK, fix this. OK, it's not airport code is departure airport run again. Column pday does not exist. Yes, because the parameter was called not pday, but it was called p departure date and again, when function was created, Postgres didn't check. Should it? No, it doesn't cut again. We pass it as a body of the function was a string and these details were not checked. Okay. So in contrast to other databases, if you have experience of creating functions as well, anywhere else, functions in Postgres are stored in form of the source code. No, they're not really compiled. Function is created. It doesn't mean it is compiled. It is stored as a source code. Functions are interpreted during execution and even when function is executed only when the execution path reaches this specific command, this command will be analyzed and prepared statements will be created and it will be prepared statements will be reused in the same session. So if you run the same function multiple times in the same session, but otherwise it won't be it will start all over again. So that means that you might not discover some errors in your function for a while. So that means that when you test the functions, you need to pay special attention. You tested all the cases because if you have multiple case statements, for example, and one of the case statements was never reached, you might never even know your function has an error. So that's kind of warning for the functions. Also, when you create, again, that's what we're talking today about PLPGSECO functions, no execution plan is created. So no checks of existence of tables, columns, or other functions are performed, whatever is referenced in the function body. And you might even know whether it works or not. So just be very careful while using functions. It's not what you expect from the programming language experience. Other thing, both these functions are atomic. So that means that you cannot initiate a begin a commit or rollback transaction inside the function. And again, for people who come from other databases, it's huge disappointment. So we will talk a little bit at the end of this session. It is done with post-restorated processes, but functions are atomic. And also you can never explain function. If you are used to running explain to see how your SQL will work. You cannot explain the function like the only thing it will give you. Okay, explain. It will say, okay, that is like the result. So if you want to know what path execution path is chosen, you need to actually run explain plan for specific statements inside the function with specific parameters. So that is important. And again, the reason originally for this atomic set is because original purpose of the function was to be able to select them in the select list. So for example, you can do this call this function. When you select something from this passengers, right? So here you have a select text to integer as passport number, select text to date, the expiration date from the passenger passport table. So that is how we can use functions. And now I want to tell you why it's not always a good idea. Precisely because of this, that functions are atomic. And it might be not so pronounced when you use functions in the OTP environment, when you select at any given moment or the small number of rows. But it might be more pronounced when you do it for the large data volume. So here we have this function. Again, create function number of passengers on specific flight. Awesome, right? So we select count from the passengers who were registered for specific flight. Great. Now, we want to use this function in select. And so what we're doing, we are doing so for each flight, which departed from Ahara on July 5th, between July 5th and July 15th, we are selecting flight ID and the number of passengers. So legit, OK, that's what we want. We want to reuse the function. So the execution time for this statement will be three and a half seconds. You might say it's not the end of the world, but honestly, that is something alarming for online application. Three and a half seconds just selecting a number of passengers.  So you'll do select flight, count as number of passengers, and you will do this grouping inside select. The execution time is actually less than a second. So by this difference, the difference is precisely because function is atomic and optimization of the function is not included in the optimization of the whole sequel. So in this case, function will be executed each time it is called separately. So that is so-called optimization fence. And that is something we just need to have in mind. So not always using functions, especially in longer queries is a good idea. Now, you might ask, OK, so that is custom function. What about simple transformation? This, you know, I always knew it's problematic. And for Postgrease Air, we built this table passenger passport specifically to show why it might be a problem on the large data volumes. So this table was created as a know-how in many systems when people do not know where to put new data. They use something like custom types, like custom defined types, special types. And they put everything in this big table with like type and qualify, etc. So that was how we built this passenger passport table. So there are lots, lots of different information in kind of key value format. So that huge table, I think, like 15 million rolls, something to mess up. So you just do select passenger ID, passport number, passenger expiration date from passenger passport, OK? So 50 million records, 41 second execution time. Just pure select, select everything from this table. Now we are selecting. So by doing simple Postgrease transformations with no exception. So we can put passport number to numeric, passport expiration date to date. Execution time is two minutes. And if we call the user defined function text to integer and text to date, the execution time is nine minutes. So that's how much difference it makes. So again, that is something to be aware. Sometimes it's not important. Sometimes it's important. Just these numbers, I think, are very illustrative on what might happen when the whole functions in the select list. Because otherwise nothing changing. We do not join this table with anything. So the select execution plan will be the same in all of these three cases. But the execution time will be very different. So now after all this, I already thought like, how horrible it can be? Is there any way functions can improve performance? Actually, they can and we just need to use them differently. Hey, and that's what we are going to show. But again, I'm keeping reiterating on this because in programming languages, you often use functions just to like code. Like, you know, separating, encapsulating the pieces of code which you need to reuse. And it's not always a good idea with database functions. Right? So we can this function, but we need to utilize slightly different approach. No code, even if we do code factoring, it will be in a little bit different place. So in order to show one of the good usages of post-bis functions, let's look at the user defined types. So I can talk about these defined types will be another class, another presentation. But now I will just briefly mention it. So Postgres allows to define user defined types, which can be as simple as create domain or can create an emulator or can create a range type or like any base type. So those are all simple types. What we want to look at today are user defined composite types. And many of you guys did it in your life. So we can create the composite type, which will be like record type. For example, here, we are creating the boarding pass record, which has like boarding pass ID, booking lag, flight number, et cetera, first, last name, seat number. So that's how your boarding pass looks like. You know, we created this, what we say, a database in the midst of pandemics. So it was a little bit nostalgic. Nobody would fly. Now people kind of like start to get there. So now people start to remember again what are boarding passes, what are connections, what are slides with false flags. So now it's like, not that foreign as a year ago. Some composite user defined type. So what we're going to do with this and what it has to do with functions. All right. So we can create functions which return composite types. And what we have here, we have like very simple function, which returns all boarding passes for the specific flight. So printing all boarding passes for all passengers. So it returns set of boarding pass records. And you look at the border of the function, it's very simple. It returns the query, select all this information we just described. And then when we execute this function, it returns the whole result set. So if you look at the result of the function, it will be like the same as you would select things from the tables. Like no, no difference from a cycle process. So why it is better? OK, you select, run select or run functions was the difference. Why it's better? So several reasons. First of all, there are parameters. So we do not need to write SQL each time. We can just pass different parameters. In this case, we can print boarding passes for any flight. Also, there may be more complicated function for it. There may be more things than just select and I won't go into depth. Here it has, and like I said, it's like whole separate topic, how you can interpret different parameters differently. Then the other important thing, it will create different type of dependencies. And I will tell about in a second and functions will allow you to execute dynamic SQL. So let's look at all this other things. So functions are not dependent. So why it is important? Imagine you have a system where you created multiple views, materialized views and something, and then you need to modify one of the underlying tables. Or God forbid, you'd like to drop and create something else. So what this would not allow you unless you drop cascade all dependent objects and they may have all dependent objects and in real systems, like in many reporting systems with views and materialized views, modifying just one element might result in cascade dropping of like 60, like 90, I had the situation when we had to recreate 95 objects when something underneath was modified. So that is because these objects are dependent and progress holds these dependencies. Functions, as we said, functions kind of do not know what is inside the function. So if you reference any table, so like if instead of materialized views, for example, or something, you're used to reference the tables, your reference other functions, they do not know whether you did something or not. So for this function, so what we're doing here, we're issuing a booking, a boarding pass for specific passenger, right? So we are actually creating this boarding pass and then we are executing query, select a star from boarding pass and the path with ID. So when this function is created, technically this function, the reference should not even exist and it would not check. And if we change something with this function, if we want to create, replace, modify, it would not cascade drop this function. So again, in systems where such situations occur, it's very convenient because like, okay, we know what we're doing, okay? And we need to know what we're doing. So otherwise actually if something significant changes and functions wouldn't be cascade drop, you'll find everything and you'll try to execute it. But in many cases, it's more convenient because you can recompile and modify all these components. However, the functions will create different types of dependencies and that will be dependencies on the types. So types, that's something we'll hopefully talk a little bit, maybe not. Okay, so the other thing which we want to talk about, functions and security. So by default, okay, all objects and database have permission so people can have like, select and sort of delete and add some other permissions on the objects. So functions, if you do not do anything, they do not have any extra security permissions on their own. By default, functions, permissions are public, which means that, okay, anybody can execute any function providing they have usage on the scheme of a function is created. And the only way how permissions will be checked is when, okay, so I'm a user. So if I have all the permissions on the underlying objects which are inside functions, so if I am granted this select, this insert, this update, then I can execute it. And if I do not have it, then I cannot execute. So the security on the function, by default, that's executed the final will be, that's literally what permissions this user have. So the situation we often want to address, you know, everybody have very important business users in the organization, right? And this very important business users always tells us, we need to see production data because otherwise we cannot make our important business decisions. And you have kind of like two options, either to give them access to the database, which, you know, you might not always be confident that important business user has all the capabilities to run SQL, which won't bring the database down. I think many of us have these experiences when very powerful business user brings the system down, this I had multiple times, so that's what we can do. So we are creating function, which is the security definer. So what it means, a user to whom we will grant permission to execute this function, then this path execution, while they are like in the function body, they will have the same permissions as the user who created this function. And then say, okay, so we are like our user, like we are DBA user in our database. So we create this function, critical function, and we do whatever we need, when nobody are doing it in the right way and nothing crazy, nobody is going to drop database or like data board information. Then we will revoke execution and dysfunction from public. That means that nobody except of the user whom we designate will be able to execute this function. And then we grant execute on this function to our very important business user. So this way, they actually can execute something critical on production data, but they won't be able to do whatever they want on production data. Only what we allow them, and basically in the way how exactly we allow them to do this. So that is actually a very powerful way of doing this. And that is something which we strongly recommend to you. Okay, next, a function with the word return. So again, for the longest time, both of these did not have procedures. Both of these only had functions. And functions technically were supposed to be like used in select statements. And in many cases, we still wanted to package some set of operations inside of like some procedural model. And for this, we used functions with word return. So function which returns nothing. For example, here we're doing like function cancel flight. It will do lots of things. It will change the scheduler. It will unknown all the boarding passes, et cetera, but it does not return anything. It's like cancel flight, okay, done. So this function will return nothing. And the other, like the other way what we're doing here, we can execute it when we execute function inside other functions. We also do not necessarily need to process return of the function. So one way is doing word function. The other way is perform operator. So we can do perform operator inside others. And then we just would not capture the return result. So that's like other way of doing this. So that was all with functions. And then finally, you know, we'll do like 10 years, whatever later, more process introduce procedures. So now as again with postgres, with same with Oracle, with Cybase, with Microsoft SQL Server, now postgres also has a procedure. So what's the difference? Okay, actually lots of things are the same, but many things are different. That's a good part. So to create procedure, we use create procedure and it's same as that function. We have procedure name, procedure parameters. Only it does not need return type because procedure does not return anything. You can define the output parameters, but again, that will be parameters. Procedure does not return anything. And you do not do select from procedure, you call procedure. So you can, for example, execute your call cancel flight. So we do not need to create function with return. You can just create procedure, cancel flight. But that's not kind of the best part because, okay, we can use void. What's the difference? So most important thing is transaction management. Finally, I can tell you a person who came to postgres from Oracle, that was like my huge disappointment, like, ah, where are my transactions? How I can manage transactions? How I can do like rollbacks? How I can commit intermediate results? Not like the next version, next version. So I remember like for seven years, like after each post conference, I was coming to one of the major contributors and asked like, so when they're going to have them? So then finally, yeah, we got them. So in storage procedures, you actually can do conditional commits. And like one of the good parts is here, for example, when you do massive updates or massive inserts, you can commit intermediate results because otherwise you have this like huge process which runs forever and takes all the temp, like all the buffers and everything, then when it fails and everything fails. So at least you can commit stuff in the middle. That's a good part. And many other reasons why committing partially inside the function is the great thing. And the other thing which functions allow is the exception processing, like multiple exception processing. So again, here what we have, we have several execution blocks within storage procedure. So we can have multiple begins, multiple ends. And for each of these blocks, we can define exception and like for example, how I use it with great success when we like literally have like lots and lots of things in one procedure. And when we raise exception, at least we know in which part of the procedure is exception was raised because otherwise you can go on and on and like, oh my gosh, when did it happen? So this way that allows you to identify directly and precisely even when the exception is the same where exactly it happens. So again, very, very, very useful tool. I think that again, procedures are also kind of underused just because then you and for many years people actually use the word functions instead of procedures. So for me that was also like kind of change of mindset. Okay, do you need to return something? If you don't need to return something just create procedures or create functions because that was also like force of habit. So I'm like encouraging people using sort of procedures when they do not need to return results. All right, and now we are up to the most fun part. And again, I could potentially talk about this forever but I am not because like if it will be separate topic I will just outline a little bit about the usage of dynamic SQL. So it is probably the main purpose of using function storage procedures to be able to execute SQL dynamically. So what is dynamic SQL? For example, like this, so we have a text. So SQL is text and what we are doing we are building the select statement here. So we assign this text variable the text of the SQL statement. Select count from booking, we are booking reference blah, blah, blah. And then we execute this SQL and we return the result of this execution. So that is as easy as this but like why it is so important because you can actually generate different SQL within the same function of storage procedure and execute something different depending for example on input parameters. Now, why it works better in Postgres? Why it looks better than parameters? So that's actually mental block for many people who come from different database systems. And I often had to work with people who come for example from Microsoft SQL Server and from Oracle in all database text books tell you do not use dynamic SQL always store parameterized statements because they optimize better. So that is works like kind of opposite with Postgres. Okay, so why it works opposite in Postgres? Because the execution plans again, there are some exceptions to all of this statement but by default most of the time execution plans are not cashed even for prepared statement. So that means like in Oracle you executed it's like sits there in the shared memory and each time the same query is running it's like here is the plan execute. So it's not the case in Postgres. Optimization always happens right before execution. And it happens like later than all in all other systems. So in Postgres, again, except for few exceptions the optimization is done for this particular SQL statement with this particular value. So for example, whether this particular set of parameters will produce massive result or small result for which of the parameters or which of the conditions will be the most restrictive condition kind of Postgres does not know in advance it would optimize for this particular values. So because of that, using dynamic SQL it's just better and more reliable especially actually for all TP systems because then the very better chances that each and single query will be executed the right way this very moment. So as I said for all TP I'm almost not touching it because it's huge separate topic but I want to show you something which actually like kind of less frequent typing probably and I find it very useful technique. So dynamic queries for a lab because with a lab so the problem I see with a lab systems people often say we don't need to optimize anything because those are reporting queries and like whenever they will finish whatever and that's what I mentioned in September when I had a presentation about long queries and after full scan. Long queries still we still want to optimize long queries. And that's one of the examples of kind of like a little bit counterintuitive optimization of long queries and that's like modeled from very real situation which I had with like working with marketing analytics. So that's a function very like okay, normal function. So what we are returning, we are returning a category. So the input parameter is integer age and we're saying, okay, so age under two, it's infant. So from two to 12 as a child, from 12 to 35 as an adult and else senior. So like function which you can easily imagine is needed for the like our airlines database. And then it is absolutely fine when we are doing it like for one record for a couple of records. Now remember we created this like huge tables they're like specifically for the purpose. Okay, model how it will work with long analytical twins. So it has lots of messages, like several million messages. So if we have here limit again this example is taken from the book, we had limit like literally so that it won't run forever. So we were just, you know, kind of making it more safe. So we select for each passenger age category and the execution time when the coldest function is at 25 seconds. And if we instead of calling this function like inside the select we are doing this case statement then the execution time was nine seconds. So like almost three times faster when they do not call this function. The function does not even do any select, you know, it's like ridiculous, right? Its function doesn't perform any select it performs data transformation, but it's still counted. So kind of our first advice was like do not call your function, do not categorize your things while you are selecting just do this, but then like, okay. So we actually want to have something like categories and functions because we use it not just in this query we use it in like 20 or 50 or 100 other queries. So how we can still do this and like how we can still make it reusable. Okay, here how you can make it reusable. So we can use dynamic function and look what we're doing here. So this dynamic function generates a part of SQL statement. It does not calculate anything. So this function again takes the age as a like a parameter because it will be some column in the table and it generates the part of SQL. So it returns a string. So remember how, okay. The board was doing a yellow cell body, okay. And the body and returns part of code, not the value. And what we're doing then? Anywhere when we need to use this age category that's what we're doing. So we are selecting passenger ID, then we concatenate. So here is the dynamic query. So we execute query. Passenger ID include this part with case dynamic category as age category and then we execute the statement. So we use function to generate a part of SQL. And this actually again, we faced it in like more serious environment where we were able to improve performance of statements like approximately four times, but that was like from an hour to 15 minutes. So it was actually a huge help. So that is like one of the example, okay. So again, that's like again, it is more pronounced when we have large datasets and we need to use almost the same thing. Again, as everything else, it's not the universal solution. And in some cases people still might want to opt doing something else, but it's one of the things I just wanted to illustrate how it works in pretty much like unexpected way. And there's still way more things to talk about functions and about user defined types and how to build that functions with like miracles, but maybe it will be an autotopic of other talk. And this concludes this presentation and thank you so much for your participation and any questions yet. Beautiful. Thanks so much, Hedy. Yes, we have two questions that have come in. The first is you mentioned that functions can't have explain. What about stored procedures? Can we see an execution plan? No, no, same story. Yeah, we can look that inside them. Easy. And the second question is a bit long. So if you want to pull up chat so that you can see it as well, I'll still read it out. Let me see, let me see. Related, oh gosh, okay. Related to explain. You mentioned that the only real way to deal with this is to explain the statement that is inside the function. But sometimes that's extremely difficult if the statement is dependent on the variables, values, results, from previous statements in the function. Yeah. Actual public support. Yes, yeah. Okay, no, there is again, there is no universal solution. So I can tell you some techniques which I use with functions. It's like debugging functions is not fun and that's why sometimes I'm saying, you know what, if it's not super critical, just do not use. But if you actually debug the function with Dynamics SQL, the results are beautiful, reliable, and it's really like, I think the only way to ensure robust performance of the application. But what are you? So first of all, like, you know, if you use a PitchetApp unit test, sorry, yeah, PitchetApp test, you can actually build. So yes, and sometimes you actually need to test just parts of the function. Again, I can talk a lot about this like testing technique. Yeah, most of the time you might need to put intermediate like printouts, like raise notice. And yeah, there is like no way unfortunately to compile. So you need to test separate parts. That's what I was doing. I was like passing, like pass parameter, captured to generated SQL. And then you need to take generated SQL and execute it. And then most of the time you are able to figure out what exactly is running slow, for example, what exactly does not slow. But you know what I might, okay, maybe we should have another presentation about how to test it. I never had it formalized. I mean, I have several like tricks how to do this, but yeah, it's like I don't have universal solution, yeah. Sorry. Wonderful. Okay, those seem to be our two questions. All right. So with that, Hedy, thank you as always. We always love having you on and at the conference in person and online for these webinars. And I want to thank all of our attendees for spending a little bit of their day with us. So with that, have a great rest of your afternoon and I hope to see you on future PostgreSQL conference webinars. Thank you.


Firstly, I would like to thank the FAO and the rest of the organizers for giving me the opportunity to share our work. On behalf of all those who have participated in any way in this initiative, particularly the members of the Silva Mediterranean Working Group on urban and peri-urban forestry, we hope you enjoy it and find it of interest. I'm going to talk about one of the main disturbances associated with urban and peri-urban forest and have been mentioned by Wendy before, which is the allergenic pollen emitted from vegetation during the flowering period, and which in fact are the main source of primary biological atmospheric particles to the atmosphere of all categories of viruses. These pollen emissions are considered one of the main functions of ecosystems that are perceived as negative for human well-being with a great effect on air quality and on the health of the population. Polling in the breathable fraction of air is considered the main causative agent of allergy response in the world population, with a higher incidence in urban environments than in rural ones. The World Health Organization classifies allergies related to the presence of pollen in the air as a perennial disease that generates recurring symptoms in any time in life. The economic cost associated with each allergy individual has been estimated at about 9,060 euros per year, including days of ascentism and presentism in school and work, and with growth expectation in recent and future years due to the impact of climate change. In urban settings, the situation can be aggravated due to the interactions with other pollutants, but also due to the diversity, intensity, and proximity of emission sources and the lack of planning when designing and managing urban forests. If we review the causes that have generated the increasing allergenicity of urban forests, we find among them the massive use of low number of ornamental species, the botanical cecism, the introduction of allocotones or exotic species, the spread of embossies, or the interaction with pollutants and the effects of the climate change. Therefore, in order to characterize the specific allergenic potential of the urban planning species and urban forests per se, and to be able to establish and implement measures to mitigate the allergenic impact, the breathing in the parks initiative was created, which has developed two tools to this purpose. The value of potential allergenicity, the BPA, and the index of urban green zones allergenicity, the EUSA. The first step is to identify the biological attributes that contribute to the allergenic behavior of an species and three factors have been identified. Since plants that use the wind as a transport vector emit huge amounts of pollen, the duration of the pollination period, since the more extensive, the longer there is pollen in the air, and the intrinsic capability of the pollen grains to generate an allergenic reaction in sensitive individuals. For each attribute, a value ranging from zero to one to three or four is assigned according to the characteristics presented by the species. The combination of these three attributes dissolved in a specific species specific value of potential allergenicity, which we will write between zero and 36 and will allow each species to be assigned a class of allergenicity, which can be zero, low, moderate, high, or very high. This value may be used as a criteria when selecting a species to be introduced in any element of urban forest. Once the value of potential allergenicity of each species is known and considering the biometric parameters of diameter and chrome height, the allergenic potential of any green element can be estimated by applying the index of allergenicity of urban green zones, which will result in a value understood between zero, like null allergenicity, to one maximum allergenicity being 0.3, the threshold value, which we will indicate the risk that obesity to this space during the flowering period of allergenic species may represent for an allergic person. And this was the origin of the reading in the Mediterranean parks initiative, in which thanks to the collaborative work of the member of the silver Mediterranean working group and other associations. The index was applied to more than 40 urban forests in six Mediterranean countries. To assess the allergenicity and as of the analysis of the most frequent families, general and botanical species in Mediterranean urban forest was carried out and the value of potential allergenicity of the most frequent was estimated finding the sun with the highest class of allergenicity are of Mediterranean origin. The application of the user to the different urban forest in Spain, and at both shows of the Mediterranean sea, allow to know aspect that participate in allergenicity, such as the design of the elements, the most contributing species, the handling and maintenance carried out all the assistance of other emission sources in their surroundings. And to conclude, and as a guidelines that can help mitigate the impact of allergenic emissions on the quality and the health of the population. We highlight the importance of the diversification of a species at all multifunctional levels, the proper management and maintenance of the species and species, and planning in the solution of a species and in the design of the space in which they will be incorporated. And before concluding, I would like to thank all the people and institutions that have made this initiative possible, so that urban forest are more possible, healthy, and inclusive spaces for all. Thank you for your attention. Thank you very much. It's so interesting. And I see that you have just published a paper in a reverse trend. Thank you very much. You're welcome. And you see we have now for cases focusing on different aspect of urban forests, but we all try to derive more diverse ecosystem services or benefit and minimize any potential negative impact. associated with urban trees. And then we are going to move to the panel discussion, we are going to have three invited thoughts highlighting their research and especially the opportunities and challenges. We are facing in using or implementing urban forests to have cleaner cities. And now we are going to have our first keynote panelists the presentation. Mr. Marak. He is and he had been an Indian forest service officer. And then he has served for the government as the principal secretary to the state government of mega liar India. And currently he is an advisor conducting green summit for North and Eastern state of India, which provide a very interesting platform or interaction platform for policymakers, implementers and grassroots workers to highlight environmental issues relevant to North East India. Mr. The floor is yours. Hello, I just got a message from Mr. Malak saying the power is down. So he tried to come back and hope he can be reconnect after power stuff. Okay, so we need to wait. So can we move the second presenter. Okay. So, okay, so now it's your time. Let's move the second presenter up. Okay. Okay, I'm good. Okay, so are you agree to agree. Yes, please continue with the next speaker. Okay, okay, so that's good. So now we will move on to the next speaker. He is an associate professor of the department of resource and environment Shanghai Jotun University. He also served as the director of Shanghai urban forest ecosystem long term monitoring and research station. His research focuses on what happened at the interface of urban forest the soil macro climate and air pollutant covering many aspects. So he can tell very successful story about how urban forest interact with environment and play there in key roles as different scales in urban environment. So the floor is yours. Please send. Okay, Wendy. Could you see the slide. Yes. Okay. Thanks, Wendy. Hello, everyone. I'm Shanyin. I'm from Shanghai Jotun University. Today I want to share some work we have done in the last few years, the title, just like this. And why I chose this title I think you know the answer must be true. The infrastructure actually could and not to it, urban air pollution. So that's why we are gathering here today, but what's the more important is how we can prove this and make it better. First air pollution. I won't talk about PM 2.5 and polysilic aromatic hydrocarbons pH as a typical air pollutants in the city. They are mainly man made pollutants, very stable and they make it long distance with harmful effects to every resistance. Urban air pollutants could deposit from the air to plant soil and water by dry and white depositions, and sometime back to the air by resuspension and release. The vegetation or plants could play an important role in the biogeochemical cycling that drive the position on leaves and absorptions are the key roles to remove air pollution. So what's the main influence of factors between air pollutants and the vegetation. A study can quote four aspects. Let's see that, including tree species pollutants, mitrological factors and plant community. If we look at, we could say tree species actually means functional trees of trees and leaves, which we could call this internal factors. And the pollutants and the mitrological factors are external conditions. These two interact with each other at the surface of leaves and branches. And also, with a well designed plant community, that's how we could have a cleaner city and a human wellbeing. So as I said, the questions are concerned. We concern about the forest and air pollutants should be seen from different scales and have different questions in the internal and external factors in the first scale. We focus on the leaf and tree species. The question is, which tree species has better capacity to remove air pollutants. And the second scale, we focus about the plant community. What are the effects of landscape design on pollutants removal? And the third scale, we should look at the whole urban area. How to play land use types to help improve urban air quality. So let's start with the first scale. What happened in trees and leaves? There are also two questions we would like to answer. So how do leaves absorb aeroparticles? And which tree species should be selected? In our study, we choose 14 type of tree species in Shanghai with four conifers and 10 broadleaved. Leaves and branches are both simple. We use a wind tunnel method and a smoke chamber method to determine the PM 2.5 dry deposition velocity on leaves and branches. And 3D X-ray microscope is used to observe where are the particulate matters on the surface and in the leaves. So let's answer the first question, how do leaves absorb particles? As the figure shows, the coagulation effect happened on the leaves, which means small particles combined and accumulate into large sized particles. And that helps leaves to absorb or fix more particles in the air. And from the right finger, the functional trees of leaves, we found that the single leaf area specifically for weight and the surface free energy were the main influence of factors of particle deposit on leaves.


Hello Bumblebees, it is I, Philip Magnus. In this, this is a tale of a ranger, a lone ranger in fact. Who has decided to take on all of Mordor? Oh, look, a vendetta mission. They allow you to avenge the deaths of other players.  Right, that sounds actually quite a lot of fun. Where are those? Hmm, I actually don't see a single vendetta mission right now. Never mind that, we've got a bit of a holy crusade to go on. And indeed we are going to rescue a few prisoners, we're going to kill a few orcs, nothing too shabby, not at all. Let's see then, a few broken tools have earned these slaves days of torture. Slay the orcs, guarding them and set them free. Oh, it will be a pleasure. As I was playing yesterday, I had a most unfortunate accident. Oh, a ledge kill. Left shift plus top. Right then. Can we do one of those then? First we need to find a ledge. As I was saying, I had a most unfortunate accident. You see, I made probably one of the best commentaries of my life. Alas, lal. Someone's sneaking about. But Xplit absolutely destroyed my footage, and that annoyed me greatly. Yes, the corpse indeed. How dreadful, how awful. Absolutely unexpected. I'm sorry, I can't. I can barely contain myself. I know it was safely to kill him stealthily, but to brutalize an orc or not. I'm always going to go with brutalize. It's in my nature, you see. I'm a bit of a saviour part, at least when it comes to orc-human relationships. I really don't like orcs or goblins for that matter. I find it very curious that Tolkien used to actually call orcs goblins in Hobbit, and then all of a sudden he completely switched to orcs, and really he completely neglected goblins. It was kind of sad. I like the word goblins. But it was not meant to be. Orcs are in their own right, acceptable, and I do not respect them any less for not being goblins. Oh boy, are we going to have a fight here? Uh oh. Let me have a few more arrows to do that, you know. So I suppose that... nah. Fine. You died too. I suppose, uh oh. Oh well. Time to hide away. Almost beautiful, come to think of it. Oh look, an elf shot. I'll take that, thank you very much. Yes indeed. I suppose I can... no? Right. This should count as a lovely way to dispose of an orc. Anyway, now I can free the slave. Hopefully he will find his way back to safety.  Yeah. I'm actually surprised they use you as slaves at all and not just kill you in the first place. But hey, do it your own I guess. Right then, on to our actual quest. And today we're going to do some climbing the ranks. We're going to help our good friend Radbag once again. And what are we going to do? Well, we're going to keep him alive. Again, sounds familiar. I know, I know. I don't make these quests up, mind. Yes, yes, man-filled. And you're orc-filled, but do I point it out? No, I just stab you in the back with sharp objects.  Ah, orcs are such unacceptable creatures. No common murderous cursesy. Wonder how long it will be until I have to put Radbag in the ground. Well, he is not in a good place right now. Yes, thank you. I agree. So that didn't count as a stealth kill. Sorry. These things happened to me. An awful lot of the time.  It's a problem with me. Why then? I think I can find the best way to go about this killing spree. And of course, the best is how it starts from the back. Are they having a debate on whether they can execute Radbag or not? That's awfully... And it's time to begin. Oh, we have an epic voice, I see. And I don't think you've got a sparkly weapon. Uh-oh. Shields are really annoying. Boy, I see. So that's going to be a bit more difficult in my last few fun times with the captain. Not too late. I'm sure I will somehow manage. That was kind of rude. But not too early. You'll get your own soon enough. Just like that guy. He got a blade thrust through the stomach. And I've heard that is particularly painful. Let's see how you take my steamy blade up Your orc bit. You know those sore shield guards? Really, really annoying. Oh, he's invulnerable to range, that's unpleasant. But he is not, however, invulnerable to several stops. And now I think it's time to interrogate this annoying little captain. Let's see what you've got. The warchiefs are the most powerful rank in the orc forces. Captains' espires become warchiefs. Some will murder, assassinate and backstab their way to the top. Others will try to gain favor or protection by serving warchiefs as bodyguards. Excellent news. This connection indicates that Brock is the bodyguard of an unknown warchief. Hello. You're my first warchief. I feel special. Let's see then. In order to gain intel on warchiefs, you must interrogate a captain or a worm. When you draw out a warchief, all his bodyguards will also be there to defend him. Fair enough. Oh, he's damaged by ranged attacks. That's excellent news. I love ranged attacks.  Even though this is not the most disgusting orc I've seen today. That honor, yes, belongs to Radbag. Damaged by combat finishes, good to know. We're going to get back to this in some time. Yes, teamwork. This keeps happening, Radbag. Oh, you want to be warchief. That was kind of obvious, I'll grant him that. Warchief he is. Delicious. I was thinking that it was your plan before as well, but here we are, me saving your life again. It's kind of becoming an annoying habit. Yeah, let's say that's how it happened. Yeah, that was not how it happened. Radbag led us up the chain of command in spite of himself. He has a gift for failure. An impressive orc nonetheless. Oh, I've got a new objective. Four warchiefs to kill. Aye, this will be delicious. Let's see then. Did I unlock anything good? I actually have an empty rune slot on the ground executioner. We'll take it for now. Let's make a few upgrades. Hmm, still drain. No, could be useful. But then again I usually hit orcs in the head. So I'll take this instead. Anything else I can buy, dearie? Not enough? Not enough at all. Hey, we are progressing rather nicely, I would say. Shall we do one more? We should not do this now. Let's actually forge the legend of the dagger, Arkon. Because hey, why not? Wait, wait, wait, what? Okay, new plan. Let's do something like this. Yes, let's confront the captain. I was terribly, terribly confused for the moment. Hmm, your imminent death, I would have to say. Yes, you are. Go tell your friends. Ashgan the Crusher. Do I have any information on one Ashgan the Crusher? Some captains can be found engaging in power struggles, including one another for dominance or proving themselves. These missions can be a good opportunity to catch the captain you're hunting in a vulnerable position. If you don't intervene, these captains will complete their missions when time passes, killing each other and growing in power. So that would have to be a no then.  Or run away. This is going to be so much fun. All going for boom. You're rather big. And even uglier, then run back. Uh oh.  Let my arrow fly. What's that? Oi, no one be calling me a dark knight. And you are currently burning. Which I bet is... No, that is true. Um, no I think not. Bye. You are easy to kill. You are terribly easy, in fact. And your orcs far too late, dearly. And far too dead, I would have to say. Oh, how I enjoy putting them on flame. He's dead. Guess what? He is terribly dead. So is you, his friend. And so is this guy. And he will never hop away again. Ha ha. Hop little rabbits. Rabbits us. Come, come. Excuse me. I had Bolvian stuck in my shirt.


So my name is Baldwin Delgado. I'm currently a senior at the University of Vermont. I'm studying Business Administration, do concentrating in accounting and management information system. Here at the University of Vermont I was able to land a great opportunity at Prosphot House Coopers in Boston and this was through a quick connection that I made with my professor. I feel like UVM does a great job in making the most of the classes small to the point where you're able to meet up with the professor very easily, communicate with them very easily through email, phone, or even through dropping to their offices. With me being able to make a connection with my professor I was able to land this great job at Pete Diversity in Boston and I'm excited for what the future has to offer me.