WEBVTT 1 00:00:00.320 --> 00:00:01.790 Hi guys and welcome back. 2 00:00:01.790 --> 00:00:02.623 In this video, 3 00:00:02.623 --> 00:00:04.760 we're going to learn about composite types 4 00:00:04.760 --> 00:00:06.470 as inputs to a function. 5 00:00:06.470 --> 00:00:09.940 How to pass in entire tables and results sets to a function 6 00:00:09.940 --> 00:00:12.630 so that there'll be operated on one by one. 7 00:00:12.630 --> 00:00:13.720 This is really useful 8 00:00:13.720 --> 00:00:16.590 because it starts to let us use our functions directly 9 00:00:16.590 --> 00:00:18.130 on our data. 10 00:00:18.130 --> 00:00:19.030 Let's take a look. 11 00:00:20.160 --> 00:00:22.990 I've got some rather scary looking data in here 12 00:00:22.990 --> 00:00:23.930 but don't worry. 13 00:00:23.930 --> 00:00:27.530 This is just the next version on our email opens 14 00:00:27.530 --> 00:00:29.040 that we saw in the last lecture. 15 00:00:29.040 --> 00:00:32.600 We've got a user stable with an ID name and email. 16 00:00:32.600 --> 00:00:34.740 We've got an email stable with an ID 17 00:00:34.740 --> 00:00:36.700 and some email content that we're sending. 18 00:00:36.700 --> 00:00:39.050 And finally, we've got the important table here 19 00:00:39.050 --> 00:00:40.720 the email opens 20 00:00:40.720 --> 00:00:45.260 which maps email ID to user ID as well as the opened time. 21 00:00:45.260 --> 00:00:47.700 So every time a user opens one of our emails 22 00:00:47.700 --> 00:00:50.000 we're gonna add a row to email opens. 23 00:00:50.000 --> 00:00:51.730 We're gonna put in the email they opened, 24 00:00:51.730 --> 00:00:53.130 the user that opened it, 25 00:00:53.130 --> 00:00:54.520 and when they opened it. 26 00:00:54.520 --> 00:00:57.180 Note that this is not how long ago they opened it, 27 00:00:57.180 --> 00:01:00.320 but when they opened it as a timestamp. 28 00:01:00.320 --> 00:01:01.770 We've got some primary key here, 29 00:01:01.770 --> 00:01:05.170 email ID and user ID as a composite primary key 30 00:01:05.170 --> 00:01:07.320 and then we've got the foreign keys email ID 31 00:01:07.320 --> 00:01:08.550 going over to the email stable 32 00:01:08.550 --> 00:01:11.270 and user ID going over to the users table. 33 00:01:11.270 --> 00:01:13.600 Now let's look at the data we've got in here 34 00:01:13.600 --> 00:01:15.320 are four users as before. 35 00:01:15.320 --> 00:01:16.920 Now we've got three test emails 36 00:01:16.920 --> 00:01:18.300 that they don't have a lot of content. 37 00:01:18.300 --> 00:01:19.490 This could be much longer. 38 00:01:19.490 --> 00:01:21.250 It could be HTML for example, 39 00:01:21.250 --> 00:01:24.120 if you wanted to store your emails like that. 40 00:01:24.120 --> 00:01:26.420 And finally, we've got our email opens 41 00:01:26.420 --> 00:01:28.910 which has your some random data in here. 42 00:01:28.910 --> 00:01:31.100 Email number three was opened by user number two 43 00:01:31.100 --> 00:01:33.240 and this time email number two was opened 44 00:01:33.240 --> 00:01:36.140 by user number two at that time and so on. 45 00:01:36.140 --> 00:01:38.580 Like before, I'm gonna go ahead and hide these two 46 00:01:38.580 --> 00:01:40.830 because we don't need to see them at all times. 47 00:01:40.830 --> 00:01:43.200 We've got the data in there. 48 00:01:43.200 --> 00:01:44.910 I've just written here a query 49 00:01:44.910 --> 00:01:46.980 that joins email opens on user 50 00:01:46.980 --> 00:01:49.250 so you can see who opened which email. 51 00:01:49.250 --> 00:01:51.210 This is just really for your information but you can see 52 00:01:51.210 --> 00:01:53.450 that every user has opened an email here 53 00:01:53.450 --> 00:01:57.000 and we've got this open time that we can't really parse. 54 00:01:57.000 --> 00:01:59.980 So now what we're gonna do is we're gonna write a function 55 00:01:59.980 --> 00:02:03.920 that given a row in the email opens table. 56 00:02:03.920 --> 00:02:05.790 So these three columns here 57 00:02:06.760 --> 00:02:11.260 is gonna give us how long ago that was in seconds, okay? 58 00:02:11.260 --> 00:02:13.820 And the function is gonna accept an entire row of data, 59 00:02:13.820 --> 00:02:15.633 not just one of the columns. 60 00:02:16.610 --> 00:02:18.180 So how do we do that? 61 00:02:18.180 --> 00:02:21.040 Well, I'm gonna go ahead and create a new thing down here. 62 00:02:21.040 --> 00:02:21.930 I'm gonna delete this one. 63 00:02:21.930 --> 00:02:24.610 I'm gonna write my function in here at the top. 64 00:02:24.610 --> 00:02:27.560 And then we're going to select down here to see the output. 65 00:02:28.630 --> 00:02:32.610 So we're gonna start with create or replace function. 66 00:02:32.610 --> 00:02:35.450 I'm gonna call it opened ago. 67 00:02:35.450 --> 00:02:36.930 So we can call it and we know 68 00:02:36.930 --> 00:02:39.750 that this tells us how long ago something was opened. 69 00:02:39.750 --> 00:02:41.700 And now here comes the interesting part. 70 00:02:41.700 --> 00:02:44.130 We're gonna first of all as we did when we used 71 00:02:44.130 --> 00:02:46.810 normal data types or built-in data types 72 00:02:46.810 --> 00:02:48.680 we're gonna give the parameter a name. 73 00:02:48.680 --> 00:02:50.820 I'm gonna call it email open row 74 00:02:51.700 --> 00:02:56.700 but the type of the parameter has to be the table name 75 00:02:56.820 --> 00:02:59.110 for which this row is coming from. 76 00:02:59.110 --> 00:03:01.500 So that's gonna be email on the score opens. 77 00:03:01.500 --> 00:03:03.283 That's what our table is called. 78 00:03:04.180 --> 00:03:07.460 Then is gonna return an integer. 79 00:03:07.460 --> 00:03:09.150 So we do returns integer 80 00:03:09.150 --> 00:03:10.080 and then as usual, 81 00:03:10.080 --> 00:03:13.393 our double dollar language SQL. 82 00:03:14.450 --> 00:03:15.653 What do we put in here? 83 00:03:17.350 --> 00:03:18.650 You have to know that there is a way 84 00:03:18.650 --> 00:03:20.640 to get the current timestamp 85 00:03:20.640 --> 00:03:24.210 as per the PostgreSQL timezone settings 86 00:03:24.210 --> 00:03:27.830 using select current timestamp. 87 00:03:27.830 --> 00:03:31.410 So assuming that the PostgreSQL timezone settings 88 00:03:31.410 --> 00:03:33.670 match the timezone of these timestamps 89 00:03:33.670 --> 00:03:35.290 which is probably gonna be UTC 90 00:03:35.290 --> 00:03:37.410 if you'll be following what I'm suggesting 91 00:03:37.410 --> 00:03:38.940 throughout this course. 92 00:03:38.940 --> 00:03:41.660 Then these two are gonna be apples to apples 93 00:03:41.660 --> 00:03:43.690 as opposed to apples to oranges. 94 00:03:43.690 --> 00:03:45.460 So we're gonna select the current timestamp 95 00:03:45.460 --> 00:03:48.910 and then what we're gonna do is gonna subtract from it 96 00:03:48.910 --> 00:03:51.410 the email_open_row.opened_time 97 00:03:52.770 --> 00:03:56.430 and that's gonna give us the opened time column value 98 00:03:56.430 --> 00:03:57.880 from the email open row 99 00:03:57.880 --> 00:04:00.640 that is currently being processed in this function. 100 00:04:00.640 --> 00:04:01.910 You can also give it an as there 101 00:04:01.910 --> 00:04:03.410 so that it looks a bit better, 102 00:04:04.280 --> 00:04:07.200 email opened ago. 103 00:04:07.200 --> 00:04:09.593 Like that if I can type. 104 00:04:10.740 --> 00:04:12.180 Now, there's a small problem with this 105 00:04:12.180 --> 00:04:13.610 and that is that current timestamp 106 00:04:13.610 --> 00:04:16.580 is essentially Postgres representation 107 00:04:16.580 --> 00:04:18.660 of a datetime object in Python. 108 00:04:18.660 --> 00:04:21.020 So we can't quite subtract 109 00:04:21.020 --> 00:04:23.470 from current timestamp an integer. 110 00:04:23.470 --> 00:04:24.890 If we run this you'll see that you get a 111 00:04:24.890 --> 00:04:26.090 type mismatch there. 112 00:04:26.090 --> 00:04:28.380 No operator matches the given name and blah, blah, blah. 113 00:04:28.380 --> 00:04:31.970 You can't do timestamp with timezone minus integer. 114 00:04:31.970 --> 00:04:32.803 It doesn't work. 115 00:04:32.803 --> 00:04:37.100 So we have to get the timestamp as an integer from here. 116 00:04:37.100 --> 00:04:40.270 How we do that is we use the extract function 117 00:04:40.270 --> 00:04:43.500 and we say epoch from current timestamp. 118 00:04:43.500 --> 00:04:45.500 And that gonna give us the number 119 00:04:45.500 --> 00:04:47.350 that represents this timestamp. 120 00:04:47.350 --> 00:04:50.670 If we run this we get that we actually 121 00:04:50.670 --> 00:04:51.910 are working with a double 122 00:04:51.910 --> 00:04:54.740 as opposed to an integer in here so we can't 123 00:04:54.740 --> 00:04:57.090 return it because whenever you subtract 124 00:04:57.090 --> 00:04:58.660 an integer from a double, 125 00:04:58.660 --> 00:04:59.910 you return a double. 126 00:04:59.910 --> 00:05:03.170 This works if we return a double precision. 127 00:05:03.170 --> 00:05:05.280 For example like this, 128 00:05:05.280 --> 00:05:07.070 but we do want to return an integer. 129 00:05:07.070 --> 00:05:08.870 We're now interested in the double-precision 130 00:05:08.870 --> 00:05:09.703 of the timestamp, 131 00:05:09.703 --> 00:05:11.020 the microseconds, and all that. 132 00:05:11.020 --> 00:05:14.990 So we can use the cast function to cast this to an integer. 133 00:05:14.990 --> 00:05:17.590 So a quick primer on cast. 134 00:05:17.590 --> 00:05:19.410 We can do as integer. 135 00:05:19.410 --> 00:05:20.930 Make sure to close the brackets there. 136 00:05:20.930 --> 00:05:23.020 And now what we're doing is we're getting the epoch 137 00:05:23.020 --> 00:05:24.480 from the current timestamp 138 00:05:24.480 --> 00:05:26.300 and we're casting it as an integer 139 00:05:26.300 --> 00:05:27.890 and then we're subtracting it from here 140 00:05:27.890 --> 00:05:30.670 and we can finally return integer. 141 00:05:30.670 --> 00:05:32.900 So now we can run this and that's gonna give us 142 00:05:32.900 --> 00:05:35.493 how long ago the email was opened. 143 00:05:36.360 --> 00:05:39.020 So now let's create another element down here 144 00:05:39.020 --> 00:05:40.900 and we can do select star 145 00:05:40.900 --> 00:05:45.740 and opened ago of email opens from email opens. 146 00:05:48.750 --> 00:05:51.370 That is going to pass in each row 147 00:05:51.370 --> 00:05:53.600 to this function as it being processed 148 00:05:53.600 --> 00:05:55.680 and we're gonna select everything else as well. 149 00:05:55.680 --> 00:05:59.150 Pressing run in here gives us the row 150 00:05:59.150 --> 00:06:01.193 as well as how long ago this is. 151 00:06:03.380 --> 00:06:04.430 This number here. 152 00:06:04.430 --> 00:06:07.380 I mean, it's 17 million seconds. 153 00:06:07.380 --> 00:06:09.690 I'm not exactly sure how long ago this was. 154 00:06:09.690 --> 00:06:12.800 So we can actually use this a bit better. 155 00:06:12.800 --> 00:06:15.260 So let's go ahead and write this differently. 156 00:06:15.260 --> 00:06:17.320 We're gonna do select users. 157 00:06:17.320 --> 00:06:19.400 Don't start from users. 158 00:06:19.400 --> 00:06:23.700 We're gonna join on email opens on users though ID 159 00:06:23.700 --> 00:06:25.300 is equal to email_opens.user_id. 160 00:06:26.670 --> 00:06:28.730 And this is gonna give us user information 161 00:06:28.730 --> 00:06:30.453 and when they opened anything. 162 00:06:31.730 --> 00:06:32.563 Well, just the user information 163 00:06:32.563 --> 00:06:34.950 because we haven't selected everything else. 164 00:06:34.950 --> 00:06:37.803 But now we can do where opened ago. 165 00:06:38.680 --> 00:06:42.957 All the email opens is less than let's say 604,800. 166 00:06:44.110 --> 00:06:47.510 Do need to move the semi-colon to the right place though. 167 00:06:47.510 --> 00:06:51.560 Nobody is here so let's say 20 million. 168 00:06:51.560 --> 00:06:53.470 That's gonna be everybody. 169 00:06:53.470 --> 00:06:57.970 And you can see here how if we select star, 170 00:06:57.970 --> 00:06:59.710 so you can see that here we're getting everybody 171 00:06:59.710 --> 00:07:02.490 but we can pick a better number like this one 172 00:07:02.490 --> 00:07:04.000 and now we only get two people 173 00:07:04.000 --> 00:07:07.080 that opened this less than this number of seconds ago. 174 00:07:07.080 --> 00:07:09.830 Remember that as well as using the function down here, 175 00:07:09.830 --> 00:07:11.700 you can also select it here. 176 00:07:11.700 --> 00:07:13.940 Opened ago on email opens. 177 00:07:13.940 --> 00:07:16.910 And that is now gonna give you that information as well 178 00:07:16.910 --> 00:07:18.620 in case you're interested in that. 179 00:07:18.620 --> 00:07:20.480 There's a lot more to learn on SQL functions 180 00:07:20.480 --> 00:07:23.870 like output parameters or tables as return values, 181 00:07:23.870 --> 00:07:25.700 variable number of arguments, 182 00:07:25.700 --> 00:07:27.120 and a lot more. 183 00:07:27.120 --> 00:07:29.420 I recommend reading through the official documentation. 184 00:07:29.420 --> 00:07:32.270 If you have the time it's worth reading through it 185 00:07:32.270 --> 00:07:34.600 but I've also linked another tutorial down 186 00:07:34.600 --> 00:07:36.510 in the resources section of this lecture 187 00:07:36.510 --> 00:07:37.760 that has a little bit more information. 188 00:07:37.760 --> 00:07:39.540 A couple of different explanations as well 189 00:07:39.540 --> 00:07:41.760 in case you want to check that out. 190 00:07:41.760 --> 00:07:43.710 All right, thank you guys for joining me in this video. 191 00:07:43.710 --> 00:07:46.653 Thank you for watching and I'll see you in the next one.