WEBVTT 1 00:00:00.120 --> 00:00:01.590 Hi guys and welcome back. 2 00:00:01.590 --> 00:00:03.300 In this video we're gonna learn about 3 00:00:03.300 --> 00:00:05.340 User-defined functions. 4 00:00:05.340 --> 00:00:07.340 We've been using the built-in functions Postgres 5 00:00:07.340 --> 00:00:09.580 for a while, but we can actually define our own, 6 00:00:09.580 --> 00:00:12.190 and they can take arguments and they can return values 7 00:00:12.190 --> 00:00:13.023 and all sorts of stuff. 8 00:00:13.023 --> 00:00:14.240 So they're really useful. 9 00:00:14.240 --> 00:00:15.140 Let's take a look. 10 00:00:16.170 --> 00:00:19.560 What we've got here is a sample data set, 11 00:00:19.560 --> 00:00:21.490 I'm opened here in db<>fiddle 12 00:00:21.490 --> 00:00:23.530 and this is in the resources section of this lecture 13 00:00:23.530 --> 00:00:26.020 as well in case you want to open it as well. 14 00:00:26.020 --> 00:00:28.540 What I've got here, is I'm creating a table 15 00:00:28.540 --> 00:00:31.410 of name, email and last opened. 16 00:00:31.410 --> 00:00:34.150 Then I'm entering four rows into it. 17 00:00:34.150 --> 00:00:37.970 Bob Smith last opened one of the emails we sent him, 18 00:00:37.970 --> 00:00:40.962 65,000 seconds ago, that's what that column stands for. 19 00:00:40.962 --> 00:00:44.310 Rolf Smith last opened an email 8 million seconds ago. 20 00:00:44.310 --> 00:00:47.490 Susan 56 seconds ago and then Anne. 21 00:00:47.490 --> 00:00:49.510 At the bottom here, I've got a SELECT *, 22 00:00:49.510 --> 00:00:50.970 which has given me the data 23 00:00:50.970 --> 00:00:53.030 that we've got at this point in time. 24 00:00:53.030 --> 00:00:55.730 Now what I'm gonna do is, I'm gonna create a new entry here, 25 00:00:55.730 --> 00:00:58.540 so that we can create and run our functions, 26 00:00:58.540 --> 00:01:01.780 and I'm going hide these in here. 27 00:01:01.780 --> 00:01:03.950 So those are just hidden, they're still gonna run. 28 00:01:03.950 --> 00:01:05.110 They're just not showing up there, 29 00:01:05.110 --> 00:01:08.100 so that we can focus on the function. 30 00:01:08.100 --> 00:01:10.850 A function in Postgres is a reusable piece of code 31 00:01:10.850 --> 00:01:12.560 that executes when called. 32 00:01:12.560 --> 00:01:15.220 It can accept arguments and return output, 33 00:01:15.220 --> 00:01:17.220 very similar to Python functions. 34 00:01:17.220 --> 00:01:20.340 They were originally introduced in Postgres 9.5. 35 00:01:20.340 --> 00:01:22.360 Right now we're in version 12 as you can see. 36 00:01:22.360 --> 00:01:24.500 And but you can use a variety of programming languages 37 00:01:24.500 --> 00:01:27.390 to write them in and functions can do basically anything 38 00:01:27.390 --> 00:01:31.230 you can do in Postgres except, handle transactions. 39 00:01:31.230 --> 00:01:34.100 So you can't commit, or rollback in functions. 40 00:01:34.100 --> 00:01:36.280 That's what stored procedures are for, 41 00:01:36.280 --> 00:01:38.090 but you can use them for everything else, 42 00:01:38.090 --> 00:01:39.028 including calculating values 43 00:01:39.028 --> 00:01:41.360 and returning them and so forth. 44 00:01:41.360 --> 00:01:43.090 When you write functions using SQL, 45 00:01:43.090 --> 00:01:44.880 you can still update and insert data, 46 00:01:44.880 --> 00:01:47.930 but you can't group them into a transaction. 47 00:01:47.930 --> 00:01:49.450 Okay. 48 00:01:49.450 --> 00:01:51.420 And so more information is in the e-book, 49 00:01:51.420 --> 00:01:53.150 if you want, you know, a detailed explanation 50 00:01:53.150 --> 00:01:56.040 on everything that transactions do and how they do it. 51 00:01:56.040 --> 00:01:58.150 And we've also got references in the resources section 52 00:01:58.150 --> 00:02:01.390 of this lecture, as well as at the bottom of the e-book. 53 00:02:01.390 --> 00:02:04.910 So let's take a look at how we can write our own function. 54 00:02:04.910 --> 00:02:06.700 Let's say that we've got this data here 55 00:02:06.700 --> 00:02:09.130 and we want to prune our users. 56 00:02:09.130 --> 00:02:10.860 This is something that's very commonly done 57 00:02:10.860 --> 00:02:12.933 with mailing lists, when a user doesn't respond 58 00:02:12.933 --> 00:02:15.630 to your emails in a long time you delete them 59 00:02:15.630 --> 00:02:17.318 so that you don't have to pay for your subscription 60 00:02:17.318 --> 00:02:19.490 for that user. 61 00:02:19.490 --> 00:02:21.050 So let's say that we want to do something like 62 00:02:21.050 --> 00:02:25.293 DELETE-FROM users WHERE last_opened > 604800. 63 00:02:29.090 --> 00:02:31.323 So, a week of seconds. 64 00:02:32.240 --> 00:02:35.110 You could just do this and, you know, that's fair enough. 65 00:02:35.110 --> 00:02:38.330 But this doesn't really mean much. 66 00:02:38.330 --> 00:02:40.800 It just means that, you know, you are deleting from users, 67 00:02:40.800 --> 00:02:42.990 where last open is greater than 600,000 seconds 68 00:02:42.990 --> 00:02:45.640 but you may not even know what that means. 69 00:02:45.640 --> 00:02:49.430 And so, instead we can call this a name, 70 00:02:49.430 --> 00:02:50.950 just as we would use Python, 71 00:02:50.950 --> 00:02:53.300 we can give our statements, a name. 72 00:02:53.300 --> 00:02:55.610 We can do the same in SQL. 73 00:02:55.610 --> 00:02:57.630 So what we're gonna do is I'm gonna cut this here, 74 00:02:57.630 --> 00:02:59.330 and we're gonna do CREATE-FUNCTION 75 00:03:01.160 --> 00:03:03.387 and I'm gonna call it delete_inactive. 76 00:03:04.380 --> 00:03:07.430 And then we put the brackets at the end, like that. 77 00:03:07.430 --> 00:03:10.650 Now we specify the return type of the function, 78 00:03:10.650 --> 00:03:13.480 just as we do in Python, with type painting, 79 00:03:13.480 --> 00:03:15.080 but here it is compulsory 80 00:03:15.080 --> 00:03:17.507 so we can see create function delete_inactive. 81 00:03:17.507 --> 00:03:19.820 And we're gonna see returns void. 82 00:03:19.820 --> 00:03:21.120 If you are familiar with, 83 00:03:21.120 --> 00:03:23.036 basically any programming language except Python, 84 00:03:23.036 --> 00:03:26.303 you'll know that void means returns nothing. 85 00:03:26.303 --> 00:03:28.370 So that's what we're doing there. 86 00:03:28.370 --> 00:03:30.560 Then we'll say AS, and now we're gonna start 87 00:03:30.560 --> 00:03:33.370 defining what the function body is, 88 00:03:33.370 --> 00:03:37.290 but the function body must be enclosed in something 89 00:03:37.290 --> 00:03:39.890 that tells Postgres that this is the function body. 90 00:03:39.890 --> 00:03:42.770 So that is the double dollar sign. 91 00:03:42.770 --> 00:03:44.730 So, between the double dollar sign, 92 00:03:44.730 --> 00:03:46.382 we're gonna put our function body, 93 00:03:46.382 --> 00:03:50.470 ie, the code that's gonna run when we execute the function, 94 00:03:50.470 --> 00:03:52.710 but at the end there's something very important. 95 00:03:52.710 --> 00:03:55.730 We have to tell Postgres what language 96 00:03:55.730 --> 00:03:57.540 we're writing the function in. 97 00:03:57.540 --> 00:03:59.800 Because you can write it in SQL, 98 00:03:59.800 --> 00:04:01.240 as we have been learning through this course, 99 00:04:01.240 --> 00:04:03.180 but there's also a bunch of other languages 100 00:04:03.180 --> 00:04:06.800 we can use like plpgSQL and others. 101 00:04:06.800 --> 00:04:09.543 So here we're gonna say language, SQL. 102 00:04:10.940 --> 00:04:12.980 So we're gonna write this function in SQL, 103 00:04:12.980 --> 00:04:14.900 and now Postgres knows that. 104 00:04:14.900 --> 00:04:19.060 What we're gonna put in here, is the DELETE FROM users 105 00:04:19.060 --> 00:04:21.501 where last open greater than 600,000. 106 00:04:21.501 --> 00:04:23.860 Okay, so if we run this right now 107 00:04:23.860 --> 00:04:25.250 that's gonna create the function, 108 00:04:25.250 --> 00:04:27.990 just as we do in Python when we define a function, 109 00:04:27.990 --> 00:04:29.290 but it's not gonna run it. 110 00:04:29.290 --> 00:04:30.920 So we do have to do something else 111 00:04:30.920 --> 00:04:34.430 in order to run the function, and that is, Select. 112 00:04:34.430 --> 00:04:36.610 Select is used for basically everything in SQL. 113 00:04:36.610 --> 00:04:38.260 And this makes a little bit less sense 114 00:04:38.260 --> 00:04:39.550 than it would normally 115 00:04:39.550 --> 00:04:42.140 but we're gonna select delete_inactive. 116 00:04:42.140 --> 00:04:43.770 And that is how we run functions. 117 00:04:43.770 --> 00:04:45.500 So if we run this, 118 00:04:45.500 --> 00:04:48.100 then you'll see that our data has changed. 119 00:04:48.100 --> 00:04:50.900 We've deleted everybody that didn't open an email in a week. 120 00:04:50.900 --> 00:04:52.430 We no longer interested in them. 121 00:04:52.430 --> 00:04:54.390 So that's what happens. 122 00:04:54.390 --> 00:04:58.530 Note the return value here of this table is void or null, 123 00:04:58.530 --> 00:05:01.750 but you still do get a column back with that. 124 00:05:01.750 --> 00:05:04.900 This is, you know, cool and it can be useful, 125 00:05:04.900 --> 00:05:07.357 but functions really become useful 126 00:05:07.357 --> 00:05:12.357 when we can pass in arguments, we can say, delete_inactive, 127 00:05:12.400 --> 00:05:14.850 where they've been inactive for longer than a week 128 00:05:14.850 --> 00:05:16.340 or longer than a month, let's say. 129 00:05:16.340 --> 00:05:19.330 So we can prune users at different points in time. 130 00:05:19.330 --> 00:05:22.943 So here we're gonna pass in or add a parameter. 131 00:05:24.180 --> 00:05:28.630 Here is the parameter name and the data type. 132 00:05:28.630 --> 00:05:30.650 And then the function remains exactly the same, 133 00:05:30.650 --> 00:05:32.360 but now we have access to this, 134 00:05:32.360 --> 00:05:35.150 as if it were a variable essentially. 135 00:05:35.150 --> 00:05:37.300 So we'll do greater than seconds. 136 00:05:37.300 --> 00:05:40.000 And now, when we select the delete_inactive, 137 00:05:40.000 --> 00:05:42.840 we can pass in the number of seconds 138 00:05:42.840 --> 00:05:44.960 that they must have been inactive for. 139 00:05:44.960 --> 00:05:47.380 So just so the data down here changes, 140 00:05:47.380 --> 00:05:48.851 I'm gonna select delete_inactive of, 141 00:05:48.851 --> 00:05:51.410 let's say, 50,000 seconds. 142 00:05:51.410 --> 00:05:54.440 And now, Susan Williams should be the only user left 143 00:05:54.440 --> 00:05:57.210 after running this function, so I'll run it. 144 00:05:57.210 --> 00:05:59.190 And you can see that that is what happens. 145 00:05:59.190 --> 00:06:00.605 Something important, though, 146 00:06:00.605 --> 00:06:03.040 when you are creating multiple functions. 147 00:06:03.040 --> 00:06:05.290 Here we're only creating one on this data set, 148 00:06:05.290 --> 00:06:06.750 every time we run this fiddle, 149 00:06:06.750 --> 00:06:08.240 it essentially deletes everything 150 00:06:08.240 --> 00:06:09.810 and recreates everything from scratch. 151 00:06:09.810 --> 00:06:13.310 That's why we keep the CREATE TABLE and insert rows above. 152 00:06:13.310 --> 00:06:15.470 But if you are creating functions in your database, 153 00:06:15.470 --> 00:06:18.320 do remember that creating a function, you know, 154 00:06:18.320 --> 00:06:19.700 it doesn't go away on its own. 155 00:06:19.700 --> 00:06:23.183 So you can always delete functions if you need to. 156 00:06:24.180 --> 00:06:28.430 To delete a function, you simply do, DROP FUNCTION 157 00:06:28.430 --> 00:06:29.610 and then the name. 158 00:06:29.610 --> 00:06:31.847 So here we're gonna say delete_inactive. 159 00:06:32.900 --> 00:06:35.300 But notice that delete_inactive 160 00:06:35.300 --> 00:06:38.300 is not the full name of our function. 161 00:06:38.300 --> 00:06:43.210 It also needs, the data types for its parameters. 162 00:06:43.210 --> 00:06:45.413 So we'll say NUMERIC in there. 163 00:06:46.250 --> 00:06:48.290 Let's say that we create delete_inactive 164 00:06:48.290 --> 00:06:51.310 without a parameter, and also delete_inactive 165 00:06:51.310 --> 00:06:53.790 with a parameter that is okay. 166 00:06:53.790 --> 00:06:56.170 In SQL, you can do that, and you can have two functions 167 00:06:56.170 --> 00:06:58.690 with the same name, but different parameters. 168 00:06:58.690 --> 00:07:00.300 So when you're dropping functions, 169 00:07:00.300 --> 00:07:02.450 that's why you need the parameters as well. 170 00:07:03.740 --> 00:07:05.932 Let's take a look at how we can return values 171 00:07:05.932 --> 00:07:07.653 from a function. 172 00:07:07.653 --> 00:07:08.620 What we're gonna do is, 173 00:07:08.620 --> 00:07:10.510 we're gonna keep this delete function, 174 00:07:10.510 --> 00:07:11.840 just as it is right now, 175 00:07:11.840 --> 00:07:15.340 but we're going to make it give us the count of users 176 00:07:15.340 --> 00:07:18.140 that were deleted or how many users were deleted. 177 00:07:18.140 --> 00:07:19.770 The first thing we have to do is, 178 00:07:19.770 --> 00:07:23.600 we have to match the return type of the function 179 00:07:23.600 --> 00:07:26.550 to the return type of the count function. 180 00:07:26.550 --> 00:07:28.104 Because we're gonna use the count function 181 00:07:28.104 --> 00:07:30.812 in order to calculate how many users were deleted. 182 00:07:30.812 --> 00:07:33.510 Count RETURNS BEGINT. 183 00:07:33.510 --> 00:07:36.300 So we're gonna make our function RETURN BEGINT as well. 184 00:07:36.300 --> 00:07:38.580 Then we're gonna do is, we're gonna create a subquery 185 00:07:38.580 --> 00:07:40.220 for this delete statement. 186 00:07:40.220 --> 00:07:43.953 So we'll do WITH deleted AS this. 187 00:07:46.290 --> 00:07:47.920 And that's gonna give us a table 188 00:07:47.920 --> 00:07:50.870 that represents the result of running this. 189 00:07:50.870 --> 00:07:54.610 We do have to add here a RETURNING * to make sure 190 00:07:54.610 --> 00:07:57.340 that the delete statement returns what it deleted. 191 00:07:57.340 --> 00:07:58.880 We've learned about the returning keyword, 192 00:07:58.880 --> 00:08:01.590 you can usually with delete as well, to give you that. 193 00:08:01.590 --> 00:08:03.410 Now I'm gonna remove the semi colon, 194 00:08:03.410 --> 00:08:05.110 go back down to the next line, 195 00:08:05.110 --> 00:08:09.373 and we're gonna do SELECT COUNT(*) FROM deleted. 196 00:08:10.810 --> 00:08:14.360 Functions are gonna return the value of the last query 197 00:08:14.360 --> 00:08:17.330 that evaluated, so here that's the Select count. 198 00:08:17.330 --> 00:08:20.000 So that's what this function is gonna return. 199 00:08:20.000 --> 00:08:22.630 We can run this to make sure it works, seems to like it. 200 00:08:22.630 --> 00:08:24.540 And now I'm gonna add a new thing down here 201 00:08:24.540 --> 00:08:27.020 so that we can run this function. 202 00:08:27.020 --> 00:08:30.017 So we'll do select Delete_inactive. 203 00:08:31.740 --> 00:08:35.160 Let's say, it is 86400 for a day. 204 00:08:35.160 --> 00:08:36.920 And we're gonna run this. 205 00:08:36.920 --> 00:08:38.670 And you can see that we get two back, 206 00:08:38.670 --> 00:08:41.060 because we deleted two users. 207 00:08:41.060 --> 00:08:43.270 If you're creating a function and it already exists 208 00:08:43.270 --> 00:08:44.680 because you've got the function name 209 00:08:44.680 --> 00:08:47.650 and the parameter already be used by another function, 210 00:08:47.650 --> 00:08:51.730 you can say create or replace function, 211 00:08:51.730 --> 00:08:53.810 and that is gonna not give you an error 212 00:08:53.810 --> 00:08:56.290 if the function already exists. 213 00:08:56.290 --> 00:08:59.360 There are more types of parameters and return values 214 00:08:59.360 --> 00:09:01.240 for functions including, you know, 215 00:09:01.240 --> 00:09:04.130 whole tables and results sets that you can use. 216 00:09:04.130 --> 00:09:05.750 So we've got a bunch of references 217 00:09:05.750 --> 00:09:07.620 in the resources section of this lecture. 218 00:09:07.620 --> 00:09:09.170 And we're also gonna look at a bit more 219 00:09:09.170 --> 00:09:10.630 in the next lecture as well. 220 00:09:10.630 --> 00:09:12.290 So thank you guys for joining me in this video. 221 00:09:12.290 --> 00:09:14.990 Thanks for watching, and I'll see you in the next one.