Maen nhw, y dyfodol y First Item of Business is General Questions. The first question is from Maurice Corry. To ask the Scottish Government what discussions it has had with Water Safety Scotland regarding its work and with local authorities to promote water safety and drowning prevention. Minister Ash Denham. Firstly I would like to pass on my sincere condolences to the loved ones of the two women who sadly died in Aberdeen on Good Friday. The Scottish Government has supported Water Safety Scotland since the launch of their drowning prevention strategy in 2018. My officials are meeting with Water Safety Scotland and the Royal Life Saving Society this week as part of our continuing engagement. While it is for local authorities to agree their own policy on water safety, last October I wrote to all community safety partnerships to support their work and to promote water safety. Maurice Corry. I thank the minister for her answer, yet, with the upcoming year of coasts and waters in 2020, does the minister agree with me that Scotland's waters must be promoted in the safest possible way, especially considering Rossford's research, which shows that 60 per cent of Scottish local authorities do not have a water safety policy? Minister. I do. Obviously, Scotland has, I think, is 90 per cent of all the standing fresh water in the UK, but we want people to be able to enjoy the amazing countryside of Scotland but in a safe way as possible. Over the last five years, the Scottish Government has provided Rossford over £600,000 in funding to deliver its annual home and water safety programme. This year, we are providing funding of £112,000, which will help to support delivery of the commitments set out in the drowning prevention strategy. We have also proactively supported the implementation of the strategy, and so we have funded and distributed water safety educational material to 2,500 school children before the summer break last year in partnership with Rosba and Water Safety Scotland. We have hosted a ministerial round table with Water Safety Scotland and sporting governing bodies in June last year. A range of actions were agreed around data-sharing, local authority engagement and education and awareness raising, which are being progressed by Water Safety Scotland. Just to reiterate on my earlier answer, I also wrote to all the community safety partnerships to encourage them to do all they can as well with Water Safety Scotland to support and implement their strategy. 2. Sandra White To ask the Scottish Government what discussions it has had with the Electoral Commission regarding encouraging EU citizens living in Scotland to register to vote in the forthcoming European Parliament elections. 2. Michael Russell I wrote to David Littington, the Chancellor of the Duchy of Lancaster, on 3 April to express my concern that citizens of EU member states might not have sufficient time to complete the UC1 forum, which will allow them to vote at the European Parliamentary elections in the UK. If EU citizens do not have time to complete and return those forums, they will be disenfranchised and will not be able to vote on 23 May. I am pleased to note that the Electoral Registration Office in Scotland has now contacted all registered EU citizens to encourage them to complete the forums. Scottish Government officials are participating in weekly meetings of the Electoral Commission advisory board where planning for the European Parliament elections is discussed. The Electoral Commission's public awareness campaign will encourage all eligible electors, including EU citizens, to register to vote by 7 May deadline in order to take part in the elections. I encourage all citizens to register and to make sure that they are registered by 7 May. Sandra White I thank the minister for that response. EU citizens living in Scotland make a hugely positive contribution economically, culturally and academically, as I have in Glasgow University, in my constituency. I know how much they actually put forward there. Would the minister agree with me that the shambles at Westminster makes the case for an independent Scotland absolutely essential, where we welcome and value those who chose to make Scotland their home and that the forthcoming European elections offers the opportunity for everyone living in Scotland to again reject Brexit? The effort is imperative for those who are eligible to register to vote by 7 May. The Scottish Government has made it very clear that EU citizens are welcome in Scotland and they contribute an enormous amount. We want them to stay and we will support them to stay. Of course, one of the great advantages of independence will be the ability to tailor immigration policy for Scotland's needs to do away with the UK Government's deplorable, hostile environment approach. The member is, as she has said, the representative of Glasgow University. Our university sector, 25 per cent of the staff in the research side of universities are from other EU countries. In, for example, the abattoir sector, 60 per cent of the employees is a 95 per cent of the vets. I could go through, Presiding Officer, a whole range of sectors I know you rather I didn't, that pointed out the dependence upon EU Labour and, indeed, in rural Scotland that dependence is particularly great. It is an appalling thing to be pleased that freedom of movement is coming to an end and it will be very damaging for Scotland if that is allowed to happen. Question 3, Neil Findlay. To ask the Scottish Government when it will assess the impact of fish and protection orders on trout and salmon stocks and rivers and lochs. The Scottish Government has no current plans to undertake a review of the 14 fisheries protection orders that are in place. Neil Findlay. We are told that we live in an era of evidence-based policymaking, so given the decline of salmon stocks on rivers with protection orders on them, will the cabinet secretary now instruct an independent scientific review of the impact of protection orders on fish stocks in Scottish rivers and lochs? Neil Findlay. The Scottish Government does a number of things that are in connection with, particularly, the situation of salmon. I am very aware of Mr Findlay's concerns about protection orders, but I have indicated that there are no current plans to review the orders. I understand from the information that I have been given that we have been giving full answers to his points when he has raised them earlier in the year. I appreciate his concerns about trout and salmon stocks, but the protection orders are most relevant to freshwater fisheries and we have specific conservation measures in place for the protection of Atlantic salmon. Dean Lockhart, to ask the Scottish Government what assessment it has made of the efficiency of ScotRail services using Stirling station. In the preceding 13 rail periods, Network Rail was responsible for half of all delays impacting on services calling at Stirling station. ScotRail was responsible for 44 per cent. £159 million has been invested in electrification, introducing brand new electric services to Stirling. From May, all Glasgow Stirling all our services will see six extra services per day, longer formations providing 4,000 extra seats per day. On the Edinburgh Stirling Dumblane service, there will be two extra services per day, with longer formations providing 1,500 extra seats per day. ScotRail must now deliver its remedial plan, monitored by Transport Scotland with independent oversight, from a railway operations expert. Dean Lockhart, let me remind the cabinet secretary that ScotRail data for March showed that less than 60 per cent of trains terminating at Stirling station were on time with 40 per cent classified as late. Notwithstanding his assurances that everything possible is being done to remedy this, does he agree with my constituents that the current level of service from ScotRail is unacceptable? Does he have a real plan to fix that? What the member ignores is the impact that infrastructures had on those particular figures during March, where there were significant challenges with points and signalling issues in the Edinburgh area that had a very detrimental impact on performance over the March period. Having said that, where ScotRail has to make its improvements, it is important to do so, and it is outlined in terms of the timetable changes in May, how that will enhance seating capacity on the particular routes that are serviced by Stirling station, and why it also now has to make sure that it implements its remedial plan. However, as I have said on a number of occasions in the chamber, we need to make sure that both parts of a railway are operating effectively, and that means that Network Rail needs to address the infrastructure issues that continue to have an impact on passenger services. Is the Cabinet Secretary aware that, while there has been disruption caused by ScotRail's performance, another major reason for disruption of passengers in the Stirling area is signal failure. Responsibility for signalling lies fairly and squarely at the door of Network Rail, which is, of course, fully reserved to Westminster. The Cabinet Secretary for Transport, the new-grade responsibility for rail should be entirely devolved so that we can have a joined-up railway system in Scotland and ensure that Network Rail is accountable to this Scottish Parliament. The member makes a very good point. As I pointed out to Dean Lockhart in the course of his supplementary question, very often members are keen to point out the failings of ScotRail, and rightly so they should be held to account for those matters, but at times they are very reluctant to point out the failings of Network Rail, an impact that it has on passenger services. I have made it very clear that the existing structural system that we have for providing rail services in Scotland is no longer suitable to serve at the travelling public, and that is why we need to see the full devolution of railways services in, to the Scottish Parliament and to the Scottish Government, so that we can ensure that both parts of a rail network, ScotRail and the infrastructure provider Network Rail, are accountable to this Parliament and to this Government, and we can make sure that it is addressing the issues that need to be addressed sooner rather than later. 5. Graham Simpson To ask the Scottish Government what it is doing to tackle violent crime. Minister Ash Denner. I thank the member for raising this issue, and he will be aware that, over the last few months, Scotland's groundbreaking public health approach to violent crime over the past decade or so has been subject to much praise from across the UK and also internationally. Violent crime is reducing in Scotland, and since 2006-07, recorded violent crime in Scotland has fallen by 49 per cent to one of the lowest levels seen since 1974. That is welcome, but no level of violence is acceptable, and that is why we are continuing to invest in the Scottish Violence Reduction Unit, Medics Against Violence and Youth Link Scotland, and there are many partners to deliver violence prevention programmes to tackle violence wherever it persists across Scotland. Graham Simpson I thank the minister for that answer, but last week, Scottish Government statistics showed that, in South Lanarkshire, offences in which a firearm was alleged to have been involved increased by 150 per cent since 2015-16, and that is the largest increase in Scotland by a mile. In North Lanarkshire, it went up by 40 per cent, so would the minister agree that this is unacceptable and endeavour to find out what is being done to stem the tide across Lanarkshire? I thank the member for raising that issue. I would agree that no level of firearm use in Scotland is acceptable. We are doing everything that we can in order to reduce that across Scotland. I will write to the member for further details on that, but I would say that police officer numbers in Scotland remain significantly above the level inherited in 2007, and that is one way that we would combat that. The number of officers in Scotland has risen over 900 since March 2007, where there has been a reduction of almost 20,000 officers in England and Wales. As usual, the rhetoric from the Conservatives on the issue does not match their record. Gillian Martin. While figures for many violent crimes in Scotland are going down, for sexual offences, they continue to rise. Sexual crimes are now the highest-level since 1971 and they affect Scotland's women the most. Can I ask what steps the Scottish Government is taking to tackle the decades-long rise in sexual offences, but what it is doing to ensure that women get support to bring their attackers to justice? I thank the member for raising what is a very important issue.  Those include a greater willingness of victims to come forward, more historical reporting and the impact of new legislation. The Scottish Government provides support for victims through targeted funding, legislative improvements and partnership working with agencies and stakeholders. We are implementing equally safe, our strategy for preventing and eradicating violence against women and girls, and our equally safe delivery plan contains 118 commitments to achieve that. In 2015, the First Minister announced a £20 million violence against women and girls justice fund to improve the experience of victims and the outcomes of those experiences. The fund also supported prevention and early intervention work, and those aims continue to inform our funding strategy. The minister advises what the level of violent crime is in Scotland compared to south of the border, where Mr Simpson's party is in power. I thank the member for that question, but, unfortunately, comparing levels of violent crime in Scotland with south of the border is not a simple process, because the use of differing definitions and collection methods for the data in both recorded crime and our respective surveys. Our statistics demonstrate that we have seen significant decreases in the levels of violence across Scotland. Since 2006-07, we have seen a 49 per cent decrease in recorded violent crime, a 51 per cent drop in the number of victims of homicide and a 55 per cent fall in the total number of emergency admissions to hospital resulting from assault. Angus MacDonald To ask the Scottish Government what action it is taking to tackle the issue of roadside litter. Cabinet Secretary, Roseanna Cunningham Littering is unacceptable. Roadside litter is unsightly. It presents a danger to other motorists and to the operatives cleaning it up. There is also a significant cost to the taxpayer taking valuable resources away from other public services. On 3 April, I announced my commitment to bring forward legislation in the future circular economy bill that will create a specific offence of littering from a vehicle that will allow for a fixed penalty to be issued to the registered keeper of the vehicle if the responsible individual cannot be identified. Angus MacDonald I thank the cabinet secretary for her reply. Clearly, the introduction of DRS will have a positive and welcome impact on reducing roadside litter. Those of us who are supportive of DRS for some time, in my case since I saw it operating in Norway way back in the mid-80s, are keen that there is no slippage in the timescale for the introduction of the scheme. Can the cabinet secretary assure the chamber that the timeline for introduction of DRS is still on track and advise when we are likely to know whether or not glass will be included in the scheme from day 1? Angus MacDonald Yes, we are on track. The commitment to establishing a deposit return scheme is well established. It is central to our wider circular economy ambitions. Of course, it is also central to the contribution that it can make to climate change emissions reductions. We continue to make progress with the design of the system, informed by last year's extensive public consultation and the on-going engagement that we have had with a wide range of stakeholders. We intend to set out next steps with the scheme's implementation shortly, during which all will be revealed. Maurice Golden Thank you, Presiding Officer. Fewer than half of the litter fines issued in the three years up to 2018 were paid. Given that, what assurance is there that the new measure on roadside litter will be enforced any better? Angus MacDonald Enforcement is always an issue when it comes to any particular change in the law, not just this one. The intention is to provide the enforcement authorities with the ability to go after a registered keeper and not have to go through the process of trying to establish who precisely in the car did the littering, at which point, if they cannot do that, it stops. At that point, there will be an issue for the registered keeper. We are hoping that that provides a better and more appropriate way in order to deal with the problem of littering from cars. Donald Cameron To ask the Scottish Government what representations it has made to Ofgem regarding its consultation on the proposed Western Isles interconnector. Minister Paul Wheelhouse The Scottish Government continues to work closely with Ofgem and others, with an interest in the proposed transmission link to the Western Isles. I will be submitting a response to Ofgem's consultation, reiterating our strong support for a 600 megawatt link. I have written and spoken to Darren MacDonald and Ofgem's chief executive, stressing that a 600 megawatt link could help to unlock the considerable renewables potential in the Western Isles, including community-based projects, and that this opportunity must not be squandered. I will reiterate those points when I meet Ofgem's chair, Martin Cave, next week. Donald Cameron Given that a 600 megawatt interconnector would greatly benefit local community groups by allowing them to develop their own wind power projects due to the extra capacity, will he join me in calling on Ofgem to strongly reconsider the case for a 600 megawatt interconnector? I support the marks that Donald Cameron made. I have already urged Ofgem to reconsider that position. I will continue to do so in relation to the formal submission that we will make. However, as the member indicates, we are recognised that, in the Western Isles and in the other island groups, interconnections are vital to deliver the kind of community economic development opportunities that we know renewable energy can bring to the islands when there are scant other opportunities to do so. It is very important that we work together to ensure that those links are installed and that it provides sufficient opportunity to develop the economy of all three island groups. Aster Alun Although I very much welcome Mr Cameron's support for this project, which is of huge importance to my constituency, I have to point out gently that it comes on the back of a near decade of intransigence by his Conservative colleagues in the UK Government. Does the minister agree with me that Ofgem should give proper consideration to the enormous socioeconomic benefits that the proposed interconnector would unlock? Will he urge other politicians, particularly those on the Conservative benches, to make that argument very strenuously to the UK Government and its energy minister? Michael Russell The member makes a very important point. As the local member is well aware of the potential for economic development in the Western Isles arising from that investment, we will certainly make those points in the submission and give an absolute commitment to Dr Alun to do that. Of course, Ofgem is not directly accountable to this Parliament, so it is absolutely important that members across the chamber, especially colleagues in the Conservative benches, can use the influence to try and encourage UK ministers to put their own thoughts into Ofgem's consideration. We are urging Ofgem to consider the full range of benefits that Lynx would provide and which we believe should be taken into account in its assessment in those cases. We also believe that that will not only develop the economy of the Western Isles but deliver cheaper energy for GP consumers as well, so it makes sense in both fronts. Rhoda Grant The minister will be aware that Ofgem will only take account of potential generation that is in the planning process and therefore community schemes that are not in the planning process because they cannot afford to do that until their capacity in the system will not be taken into account. What is the Scottish Government doing to measure the potential generation by communities in order to inform Ofgem's decision? Michael Russell Well, the memory is an important point. We are working very closely through the channels of the care scheme to try and support communities with projects, but she is absolutely right that without the grid connection that undermines the argument for investment. It is vital that we have the grid connection in the Western Isles to allow projects that have been grid constrained since 2007, which has been a major constraint in development in the Western Isles to take place. We do know that between Lewis Wimpar and Eucinus projects in Western Isles, there are 360 megawatts already in the planning system. Further capacity is in terms of the Lethan Windfarm and other consented projects, so that is in our 49 megawatts. In total, more than 400 megawatts are already in the system, and we believe that Ofgem should be a bit less risk averse in pushing the boundaries on what is possible. The area has a huge potential for renewables, including we believe that many more projects could come forward.


Note, this document has been designated a lathe-class info hazard. Your access has been logged and you will submit to an examination to assess its impact no later than two days from this moment. If you have accessed this document unintentionally, cease reading immediately and contact the Cognito Hazard Research Group. Item Number SCP-022-J Object Class Keter Special Containment Procedures Due to widespread public knowledge of SCP-022-J, containment of SCP-022-J is limited to mitigating the damage caused by its effects. To this effect, the following procedures are to be enacted. The availability of SCP-022-J is to be reduced by means including but not limited to, suppressing research into more efficient methods of refining SCP-022-J ores, limiting the mining of areas rich in SCP-022-J ores under the guise of, for example, protection of endemic species. Suppressing front companies to artificially drive up the price of SCP-022-J in order to limit the damage due to effects of SCP-022-J-1. Suppressing public awareness of SCP-022-J's physical properties to prevent SCP-022-J-2 effects from manifesting. To this effect, media containing counterfactual information with regards to the physical properties and proper uses of SCP-022-J are to be reduced and propagated. Suppressing public awareness of SCP-022-J's anomalous properties. Treatment of individuals severely affected by SCP-022-J-2 by Class B amnestics. Because the risks SCP-022-J-1 poses to Foundation personnel, especially those in charge of designing containment procedures, any containment procedures proposal containing relevant keywords are to be checked by personnel immunized against SCP-022-J-1. Engagement agents of choice, such as chocolate, beer, diazepam, Xanax, cannabis, are to be available to such personnel to mitigate the effects of SCP-022-J-2. Description SCP-022-J is the pure, solid form of chemical element commonly known as titanium, of atomic number 22, and average molar mass of 47.86 grams per mole. In its active state, SCP-022-J appears as a shiny, hard solid of density approximately 4.5 grams per cubic centimeter, and melting point of approximately 1,940 degrees Kelvin. SCP-022-J behaves as a paramagnetic metal in most aspects, and possesses a tensile strength of roughly 434 megapascals, remarkable furtzlo density. SCP-022-J is an info hazard. Most about it causes the manifestation of two distinct sets of symptoms, designated SCP-022-J-1 and SCP-022-J-2. SCP-022-J-1 initially manifests when an individual with no prior knowledge is exposed to incomplete or erroneous information about SCP-022-J's physical properties, and its recommended usage. Individuals under the effects of SCP-022-J-1 will gradually begin to assert the superiority of SCP-022-J as a structural material, and recommend or attempt to enforce its use as such whenever possible, even in cases where this has a significant negative effect on functionality. Common terms used by affected individuals with referenced SCP-022-J include strongest metal in the world, super tough super metal, refined impenetrable metal, and the he-man of materials.  SCP-022-J's role in the incident, inventing increasingly bizarre alternative explanations.  It is exposed to accurate information about SCP-022-J's physical properties and correct recommended usage guidelines.   This effected by SCP-022-J-2 displays signs of anger and mental distress at mentions of pure titanium or its usage, to the point of becoming physically violent.  There's no SCP-022-J-2! There's nothing wrong with me!  You'd have punched him too!  There's a titanium conspiracy to sell more titanium!


guys subscribe and turn notifications on leave a like and drop a comment down below get here first and win giveaway calls i will pin as first comment on some of my videos be fast and good luck what is going on guys welcome back to the channel and i appreciate you being here today i am back with another destiny 2 video and today we have top 5 wtf moments and damn some of these are absolutely ridiculous but guys if you do enjoy the video leaving a like really helps out and i do appreciate the support and also if you have any amazing clip to send in send it into the email just linked at the top of the video description well let's get straight into this top five number five we have a clip sent in by mrbk youtube who invades and smuggles back with him acyclops being friendly on his side too awesome welcome back have a clip sent in by slayer luck how to break the wall of wishes three we'll have a clip sent in by yugdug i think his thunder crash might be broken also shak sans epic in japanese we have a clip sent in by shave roll at this point you just back out take the 15 minute ban and reevaluate your hunter's life you have a clip sent in my dark lag you ain't got a choice here you are invading son get to work and guys that is the end of this top five wtf moments if you enjoyed the video leaving a like really helps out remember if you have any amazing clip to send in send it into the email address linked at the top of the video description well guys thanks as always for stopping by and hopefully i will see you on that next one


Okay so this talk is going to be held in English, right? Yes. And that means you probably, nobody probably has a problem with that. Unless you do then please ask the German English-speaking person next to you to translate. They are now your translator. Yeah, welcome to the Rube Goldberg database, Postgres and all the nice features and things you can do with it. If like me you used Beamer again, okay? I touched nothing. Well it looks like the Beamer has died on us again because the light is out. You have the picture back there. Last time we inserted some Q&A but at the beginning of the talk this is going to be tough. Okay, so the technical is informed. So you have the feed back there? You have it on your screen? Okay. So you can hold the talk for the stream but not for the people actually here. But everybody has phones, you can just see the stream. Yeah, let's see whether this works. Yeah, let's give it five more minutes. Yeah, let's resume the stream in like five minutes or so and hopefully the Beamer will be back up then. And in the meantime we're gonna have like room party here and the stream is gonna have some party music.  Da sind noch freie Sessel und da kommen noch Leute. Und einer ist da vorne noch frei. Danke, danke, ihr seid großartig. Ich hab sie auch verschreien müssen. Ich hab vorhin erzählt von Puten Beamer und bam, kurze Update, es ist noch nichts passiert. Jemand ist in dem Raum da oben. Update, we're good to go. Okay. So, stream wieder da. Yes, also welcome zum Vortrag postgressen, was man damit cooles machen kann, wenn man ein bisschen tiefer eintrifft. Und damit meine ich, this talk is gonna be held in English. So I should also talk English. Yeah, Postgres and the nice things you can do with it. If you started like most people with MariaDB or MySQL, and then you'd start procedures and function with it and then got to know Postgres and switch to Postgres, but didn't have time anymore to go in depth there, then this talk is for you. So, yeah, you have a lot of things to do with Postgres. So you have some experience. Thank you. Okay, so welcome. So I realize, if you have to explain your title, it's probably not a particularly good title, but I've noticed a lot of people don't know what a Rup-Golberg machine is. So a Rup-Golberg machine is some kind of contraption to perform a rather simple task in an indirect and overly complicated way, usually involving some chain reactions. This is one of the original sketches by Rup-Golberg himself. And by the end of this talk, you'll hopefully have some idea on how you can build some pretty Rup-Golberg-esque things using Postgres. Okay. To start, let's just look at some built-in functions of Postgres, so we kind of get a feel of what we're dealing with here. So you've probably seen them before. They have a name and you supply arguments to them in parentheses, and one of them, like, let's pretend we have some table called users, and you choose it as an ID, a username, and we can then, for example, use the function lower to convert a string to lower case, which is kind of obvious. Yeah. And there's one thing I'll be using in the next few slides, which might not be obvious. If you're not actually getting any data from any table or something, you can just leave out the from clause. So if you just have some expression you want to evaluate something like 2 plus 2, but also if you just call a function, you can do it like this. You can just select and write the expression. So just so the next example to make sense. So we've already seen lower. Lower takes an argument of the type text in a lot of other languages that would be called a string, and it also returns a text. And yeah, that's what you expect it to do. There's, for example, also abs for absolute.  So if you give it a negative number, you get the corresponding positive number. And the way I've written these down on the slides kind of suggests something. It suggests these functions have static types, and they do. So the types of the arguments and of the return like you have to define them when you define a function. That kind of raises one question. What happens if we use the wrong type, right? So what happens, for example, if we try to get the lowercase version of the number five? So okay, we get an error. That makes sense, I guess. And there's a lot of text here that's kind of saying, okay, the function lower for integer does not exist. And there's a hint where it says like, oh, you might do typecast or something. There's one thing this isn't saying. It isn't saying like that it's inherently illegal or something to try to lowercase a number. It's just saying it couldn't really know what to do with this. And this suggests one thing which is functions can be overloaded. So you can have multiple different definitions of a function for different types of arguments or also for a different number of arguments. And depending on what you then provide, Postgres will call the appropriate implementation. So let's look at a built-in function that has some overloads. There's the function round. You give it some numeric value and it returns another numeric value. So for example, if you run this number, point five will be rounded up and you get 1312. And there's a different overload of this function which actually takes two arguments. It takes one first argument value and s, I don't actually know why s, but that's kind of the precision to which you want to round. So if we take this already kind of crude approximation of pi and round it to two digits of precision, this is what we get. And depending on whether we call it with one argument or two arguments, it will call the correct implementation or we'll complain that it doesn't have an implementation that matches the types. Okay, let's look at one third last function which is coalesce. It's kind of weird one. You can use it to kind of provide default values. So let's say we have some table called product. Each product has a name. It might have a description. It might also have a long description. It might have neither. And you can use coalesce kind of to default back to other values. So what coalesce will do, it will return the first argument that is not null. So if description isn't null, it will return description. If description is null, long description isn't null, it will return long description. If both of those are null, it will return the string, no description. And yeah, one thing we can see with this function is, and I've kind of written it down that way, functions can be variadic. So you can have functions which take a variable number of arguments. One last thing to note, there's something that kind of looks similar but works in a different way. That's aggregate functions like sum or average. They don't operate on a single value, but kind of on a set of values. So for example, if you use group by, then calculate the sum of some value for each group. They are implemented in a slightly different way. You can also implement your own aggregate functions, but we won't be talking about that. Okay, building your own functions, how are we going to do this? There's a bunch of different languages available for this. There's for example, PL, PGSQL, that's the Postgres procedural language. You can also use Python or Perl for example, and there's a bunch more languages you could use. Some of them built-in, some of them available as like third-party packages. But yeah, for this talk, we'll stick to PL, PGSQL, because I think it's easy enough to understand, and it is available by default, so you don't have to enable anything, install anything. It's just right there. PL, PGSQL is a superset of SQL, so you can just have SQL queries in there in your program. You can just have a select or an insert in your program. You don't need to have any special handling of that. But in addition to the standard Postgres SQL, it also has things like statements or loops, stuff like that. Okay, so let's actually look at an example of why you might want to have a function, your own functions. So the example we'll be looking at is, let's say we have some tables which contain recipes or something like that. They don't really have recipes, but it's just an example. So let's say we have the table ice cream recipes. Each ice cream recipe has a name of the type text and a column ingredients, which has the type text array. So it has an array of text values. So for the first line, the name is the string chocolate, and the ingredients are, like, don't get confused by the curly braces. That's kind of the Postgres way to write down arrays. It's the array of the string milk, and then the string sugar, and then the string chocolate. Okay, let's say we don't particularly like Hazelnut. So if we want to get all of the recipes that don't have Hazelnut in them, we can do something like this. So kind of the interesting thing here is the third line. And what we're doing here, it's kind of a weird syntax, but that's basically the Postgres way to check whether an array contains a certain value. You do, like, you do the value equals any and then give it the array. So basically, another way to say it is, does the array ingredients contain the string or, like, the element Hazelnuts or any element equal to Hazelnuts. Yeah, and this probably does what we wanted to. We get the one recipe back that doesn't have Hazelnut in it. Okay, let's say we have a second table. And let's pretend like there's a slight difference here. Let's pretend this is for legacy reasons. We can't change it. It doesn't really matter. Which is in this case, ingredients isn't actually an array of text, but it's just one long text. So for the first recipe here, it has the name cheesecake, and then it has ingredients, which is just one long text with the, like, flour comma space, sugar comma space, and so on. Which, yeah, probably not a particularly good way to store ingredients, but we stuck with this. So if we want to filter out anything with Hazelnut here, we need a different query. So one way to do it, and again, not a great way, but certainly one way is to use, like, a fuzzy string comparison by using the like operator. And these percent signs are wildcards. What would be, like, an asterisk in regular expressions, for example. So what this will do is it will match any row where the string ingredients does not contain the substring Hazelnut. You kind of think about where that might go wrong. It doesn't go wrong in this example, so that's fine. Okay, but, okay, what could we do with this? Instead of doing these two different queries, we could actually write a function. The idea is we can create a function. We'll call it contains. And as its first argument, we'll give it some container, and as the second argument, some element. And we wanted to tell us whether this container contains this element and return a boolean value. And we want to support different kinds of containers, different types of containers with function overloads. Okay, let's look at how we would do that for the text case for the, where we're doing this with string comparison earlier. There's a lot going on here, so let's go through it part by part. The first two lines are the signature of the function, where we give the name of the function, and the arguments, and the types of the argument. So we're saying one day function called contains with first argument called container of the type text, and the second argument called element also of the type text. And this function will return a boolean. Then there's these weird dollars. These dollars seem to throw people off, but they don't actually do anything interesting. They just delimit the part that is actually written in the language we're using to define the function. That's just basically string delimiter here. And this is the actual code of our function. We don't need to worry about the begin and end things. That's kind of the structure of Postgres, PL, PDSQL functions. And the last bit is just we need to tell which language we've written this in, because there's a bit of different languages available. And the code bit here, these vertical lines are string concatenation. So what we're doing here is we will do a comparison of container, like, and if we give it the argument, hazelnut again, for example, it will just create a string concatenate a string percent hazelnut percent again, as we saw earlier. Okay. So this works, trust me on that. Let's now do the same for an array of texts. Now there's not that many things that changed, actually. The types of the arguments are a bit different. Now we have a container that is not just a single text, but an array of text. And we're doing a different kind of comparison. But it's basically the same comparison as we saw earlier for the query. Yeah. And the rest is the same. Okay. And with this, we can write a different query where we use this function. So we're doing, again, kind of doing the same thing as earlier, we're getting all the recipes, which do not contain hazelnut. And this is the query for cake recipes. But the nice thing is this same kind of query also works for ice cream recipes because we just need to swap out the name of the table and contains the right implementation of the contains function will be used, whether the container is a text or an array of text. And of course, this can be also reused in other queries or extended for more container types, stuff like that. Okay. So what are our takeaways from this? These can help make our queries more generic, which can actually, for example, be helpful if we dealing with a query builder or something that needs to generate queries that work for specific tables. In the generic case, we need to actually know which type the ingredients have something like this. If we do it like this, it just needs to know that the contains function is going to work for this table. But it doesn't actually need to know the specifics, which can make this a bit easy in some cases. And of course, with functions in general, they let you write complex logic once and use it in a bunch of different places. As I mentioned earlier, you can also have SQL in there. So there's some cases where this can help you reduce round trips. So for example, if you have some select query and then some later query where you're using the result of the select, if you do that in your client code, you have to wait for the select to go over the network to get the result back, then do some processing in your application, then emit the new query. Whereas if you do that in a Postgres function, it can all just happen on the database and you don't have that network latency. And yeah, as I mentioned, they're much more powerful than we saw earlier. You can do inserts, updates, leads. You can also do schema definition language stuff, like creating tables, deleting tables, stuff like that, which kind of leads us to the downsides. They may do kind of unexpected stuff. So usually, you might have functions which do data modifications when you don't really expect it. And of course, to see whether the function do something like that, some knowledge of PLPDSQL is needed. Yeah. And I guess kind of the main points I think that kind of helped combat this is if you are using custom functions in your database, you should write some pretty good documentations for it to say what your function actually does and how it's intended to be used. And if your function does some data modification, delete something from a table, it should probably be obvious from the name. Okay. Let's move on to triggers.  And with these trigger functions, you can, for example, intercept queries and reject them if some you decide they shouldn't go through there invalid. Or you can also do stuff like modify the values that an update will actually need to. And yeah, one interesting thing for triggers for updates, you'll have access to the values before and after the update. So what would have been in the table before you run the update query and what would be in it after if it goes through. And let's again look at an example. Here we'll look at some like doing a change lock. So the idea is to write all the updates we do to some table to a second change lock table. So our main table will call it article. Each article has an ID and a body of the type text. And there's a second table article change lock where we will be using the same IDs and at a timestamp and an old body and the new body. So for each update we do to article we want a record in here with the body before the update and after the update. So this is the function we're going to use. There's again quite a lot of similarities to what we saw earlier in the basic structure of it. We have a function with no arguments in this case and the kind of weird special magic return type of trigger. So Postgres knows what to do with it. And then again he's kind of the main code. And kind of the only thing we're doing here is do an insert into the article change lock table, into the columns ID, time, old body and new body. And as I mentioned you have access to the values before and after the update. And that happens through the old and the new namespaces, I guess. And what we'll insert into the articles change lock is we'll just use the new ID, we'll just say we aren't going to change any IDs. So that would be bad. We use the current timestamp, that's a variable that will always contain the current time of the transaction. And the old dot body, so the value of body before the update and new dot body, the value of the body after the update. And then we'll say return new. It doesn't really matter in this case, but yeah. So this is our function. Now we want it to be automatically called for updates. So how do we do that? We say create trigger. We give the trigger a name, we'll call it log update. We specify when it or which conditions it's supposed to be run. So we say after update. And this could also be something like before update to intercept update queries or something like after delete might also be a thing you can do. Or actually one kind of weird option is to do instead of. So that will lead to the usual update logic not actually applying at all. And you're kind of left to do whatever you want with it in your trigger function. We specify the table on which we want this trigger to live. And in this case, we want this trigger to run for each row. So if we do an update that affects a lot of rows, our trigger function will be called once for each of the rows. Yeah. And we say it should execute the function log article update. Okay. So let's say this is the article table before our update contains just one post with the text hello dpn. Then we do an update and we change the body to goodbye. And this is what the two tables will look like this after we did this. So as we expect, the articles table now contains like the article 42 now contains the body goodbye. But we also have a record in the article change lock table, which contains the timestamp, the before and after values of the body. Okay. So what are our takeaways from this? We can move some pretty complex functionality into the database. Of course you could also do this kind of behavior in your client application by like checking the values before and then like writing both to the actual table and then to the change lock table. But of course, like this, we do all of that in the database and you can use this to ensure some pretty complex consistency criteria as well. You're not limited to what you can do with constraints, but you can basically do arbitrary checks on the queries and decide whether this is like something you want to consider valid or not. And what I mean by ensure a consistency across clients is you can't forget to call it trigger, right? If we have the change lock example and you have different applications writing to the same database, you might end up in a case where there's some edge case where something doesn't actually get written to the change lock. With triggers, you can't really run into that case. And the last bit is a bit of lie. You don't get anything for free. But what I mean by this is you get the same guarantees as you would in a transaction. So in another word, the trigger functions run in the context of the statement that caused them to be run and these are atomic. So if an error occurs in your trigger function, the whole statement will be rolled back. So you can't end up in a case where you've changed the article to tables, but it hasn't ended up in the update because something went wrong. Okay, but again, there's some downsides. It's kind of spooky. Like you usually don't expect updates in one table to affect other tables. And that might make things a bit harder to debug and test. But to be fair, in my experience, having code that heavily interacts with databases, testing that is already kind of tricky. So this doesn't make it much worse, I think. But of course, this can also negatively impact your performance. So because these are run in the context of the statements, if you do something that just takes a long time, some calculation that is complex or something that just does a lot of changes, that adds latency to your initial query. So especially compute expensive things probably shouldn't be happening in the database trigger. So yeah, let's finish with some closing thoughts. With great power comes great Rube Goldberg potential. So you can build some pretty weird and wonderful things with this. But you can also build some really confusing things with this, I think. But yeah, I'm excited to see what people come up with because I feel like this is a bit of an underused, underappreciated feature. But kind of what I think you should do before considering this is look at some other prosperous features that exist. There's a few cases where you can use views or materialized views to actually get kind of similar results, not the example we looked at, but other things where you might consider using triggers. And before you define your own function for something, you should probably check the built-in functions that exist and whether you can make it happen with those. There's a few really good ones in there. But yeah, I think this, using triggers and functions can be super powerful and I think it's a really elegant solution for some kinds of problems where you'd have pretty complicated code in the client that does some pretty weird transactional stuff where you really have to watch out that you don't have any weird things at cases where you miss something, which are made a lot easier with prosperous triggers and functions, I think. Okay, that's it. Have time for one, maybe even two questions. If someone has a short question, raise your hand and yeah, I will run and get you the hopefully now charged microphone. Nope, you'll get mine. Do these trigger support recursion or is it possible to run into issues with that? I think the question was, do the trigger support recursion and do you run into issues with that? Well, in general, functions in Postgres do support recursions and of course, yeah, you can run into problems with that. I don't think there's any tail recursion optimization or something like that in there. Although you usually wouldn't call a trigger function directly, but you'd usually have it called through a trigger. But yeah, you can run into problems with that. Yeah, so is there one more question, maybe? Yeah, okay. Is it Turing complete? Yeah, yeah, yeah, yeah, Postgres functions totally Turing complete. All right, nice. One more. I was expecting one more thing. In my world, the other recipes but the ice cream, I would have expected something like string split by commas and then something contains functionality available. That would be the much cleaner way to do it. Yeah, you're right. So there is actually built in function to split a string with a sort of like a longer certain character. And that would be the better way to do it. Yeah, this was just to have the two different queries and just kind of directly translate them into easy to understand functions. But yeah, this isn't how you should build your recipe database. All right, that's it. Thank you very, very much. Yeah, thanks. Thank you all for being here.


What's up guys Michael here with Jaspy's doing a quick recap of our six box. Sorry. There we go six box multi-year baseball mixer random team number one Had 2023 tops finest 22 Bowman Chrome 2020 Bowman Draft first edition 2022 luminaries 2022 logo factor in 2021 Bowman Chrome sapphire the fact that I remembered that and it's not even It's just off memory off the dome off the dome piece Take a look at some of the hits here Palacios auto dermis Garcia auto Yendry Rojas first Bowman auto JT Schwartz Had a nice O'Neill cruise orange logo factor out of 25 for the Pirates coulda Senga Morel out of 199 Durand out of 250 Bobby Whit Roger Garias green shimmer Bowman first out of 99 Yankees Luis Robert Some other parallels and stuff Keston Quira Quira out of 99 here a here a Mick Abel out of 75 her make a bell just a bell. I think Andres Jimenez out of 125 Vladdy out of 99 the Adley And then some of the first edition stuff CJ Abrams out of 75 Robert Poisson out of 50 gold and The cart from luminaries was a Wade Boggs out of 15 rays edition And that does it for that break. Thank you to everyone who got into it Thank you guys for watching and I'll see you guys in the next one. Bye guys