WEBVTT 1 00:00:00.330 --> 00:00:01.740 Hi Guys, and Welcome back. 2 00:00:01.740 --> 00:00:04.880 In this video we're going to talk about stored procedures 3 00:00:04.880 --> 00:00:07.950 in PostgreSQL and how they are different from functions. 4 00:00:07.950 --> 00:00:10.380 And really the key difference is that a stored procedure 5 00:00:10.380 --> 00:00:13.295 has access to transactions and transaction control, 6 00:00:13.295 --> 00:00:16.160 and it cannot return values. 7 00:00:16.160 --> 00:00:18.080 However, a function is the opposite. 8 00:00:18.080 --> 00:00:19.950 It doesn't have transaction control, 9 00:00:19.950 --> 00:00:21.640 but it can return values. 10 00:00:21.640 --> 00:00:24.160 So let's take a look at an example. 11 00:00:24.160 --> 00:00:25.960 Here we've got some test data 12 00:00:25.960 --> 00:00:27.240 that we're adding into our table. 13 00:00:27.240 --> 00:00:29.310 Let's say that we wanna run this every time 14 00:00:29.310 --> 00:00:31.700 that we run our applications tests. 15 00:00:31.700 --> 00:00:34.300 So we're gonna be calling this over and over again. 16 00:00:34.300 --> 00:00:37.690 We might be tempted to create a function to do this 17 00:00:37.690 --> 00:00:38.810 or something like that. 18 00:00:38.810 --> 00:00:40.830 But it's actually better to create a procedure 19 00:00:40.830 --> 00:00:44.180 so that all of this can run inside a transaction 20 00:00:44.180 --> 00:00:47.140 so that it runs all in one go essentially. 21 00:00:47.140 --> 00:00:49.950 So let's create a procedure for this. 22 00:00:49.950 --> 00:00:52.930 Creating a procedure is very similar to creating a function. 23 00:00:52.930 --> 00:00:56.330 And indeed in PostgreSQL, they are treated very much 24 00:00:56.330 --> 00:00:59.720 the same, but the word is slightly different. 25 00:00:59.720 --> 00:01:02.770 We're gonna create procedure, 26 00:01:02.770 --> 00:01:04.180 and then we're gonna give it a name, 27 00:01:04.180 --> 00:01:06.113 in this case, insert test data. 28 00:01:07.209 --> 00:01:09.050 Oh, data, there we go. 29 00:01:09.050 --> 00:01:10.970 You can put in parameters inside the brackets 30 00:01:10.970 --> 00:01:12.910 just like we can with functions. 31 00:01:12.910 --> 00:01:14.940 Then we're gonna as dollar-dollar. 32 00:01:14.940 --> 00:01:16.890 And then at the end, we're gonna do dollar-dollar 33 00:01:16.890 --> 00:01:19.530 language SQL, just like that. 34 00:01:19.530 --> 00:01:22.030 Okay, so now if I run this, 35 00:01:22.030 --> 00:01:24.920 you'll see that we get a couple errors. 36 00:01:24.920 --> 00:01:26.930 The first one is understandable. 37 00:01:26.930 --> 00:01:28.790 Select star from test data and fails 38 00:01:28.790 --> 00:01:32.560 because although we defined the procedure, we never ran it. 39 00:01:32.560 --> 00:01:34.800 But why is there an error here? 40 00:01:34.800 --> 00:01:38.720 It says, relation test data does not exist. 41 00:01:38.720 --> 00:01:41.720 Insert into test data failed. 42 00:01:41.720 --> 00:01:45.146 Well, why did it try to insert into test data 43 00:01:45.146 --> 00:01:47.680 if we haven't run the procedure yet? 44 00:01:47.680 --> 00:01:50.230 And that is because when you use language SQL, 45 00:01:50.230 --> 00:01:52.690 we forgot a semicolon here that we should probably include. 46 00:01:52.690 --> 00:01:57.070 When you use language SQL, it tries to validate the contents 47 00:01:57.070 --> 00:02:00.160 of the procedure when you define it. 48 00:02:00.160 --> 00:02:03.610 That is sub-optimal, I would say because clearly 49 00:02:03.610 --> 00:02:05.740 many procedures that you're gonna define 50 00:02:05.740 --> 00:02:08.750 are going to be creating data or you know, 51 00:02:08.750 --> 00:02:10.530 creating tables and things like that. 52 00:02:10.530 --> 00:02:12.340 And you don't wanna validate those 53 00:02:12.340 --> 00:02:14.190 when you create the procedure, you wanna validate those 54 00:02:14.190 --> 00:02:16.100 when you run the procedure. 55 00:02:16.100 --> 00:02:20.460 And so this is a limitation of the plain SQL language. 56 00:02:20.460 --> 00:02:23.900 So instead, we can use plpgsql. 57 00:02:23.900 --> 00:02:26.930 This is the parallel PostgreSQL language 58 00:02:26.930 --> 00:02:28.540 that is very similar to SQL, 59 00:02:28.540 --> 00:02:30.410 but it does have a few improvements. 60 00:02:30.410 --> 00:02:32.840 This is the language that the official PostgreSQL 61 00:02:32.840 --> 00:02:36.180 documentation recommends for procedures. 62 00:02:36.180 --> 00:02:37.410 And it's also a bit more powerful, 63 00:02:37.410 --> 00:02:40.100 it can do a bunch more stuff than just plain old SQL. 64 00:02:40.100 --> 00:02:45.100 So now if we run this, we get our result here. 65 00:02:46.260 --> 00:02:49.640 The error in grey table occurs because PostgreSQL is aware 66 00:02:49.640 --> 00:02:53.100 that we haven't started a transaction before trying 67 00:02:53.100 --> 00:02:54.950 to run SQL code. 68 00:02:54.950 --> 00:02:56.150 So we do have to do that. 69 00:02:56.150 --> 00:02:59.470 We do begin in order to start the transaction 70 00:02:59.470 --> 00:03:02.610 and in order to terminate the transaction. 71 00:03:02.610 --> 00:03:05.890 Notice that in plpgsql begin does not use 72 00:03:05.890 --> 00:03:07.300 a semicolon at the end. 73 00:03:07.300 --> 00:03:09.560 That's just a difference between the two languages. 74 00:03:09.560 --> 00:03:11.980 If you were using SQL, you would 75 00:03:11.980 --> 00:03:14.003 have a semicolon after begin. 76 00:03:15.370 --> 00:03:19.490 Okay, so now that is happy, and it will create our table 77 00:03:19.490 --> 00:03:21.610 when we run the procedure. 78 00:03:21.610 --> 00:03:23.710 But we haven't actually ran the procedure. 79 00:03:23.710 --> 00:03:26.280 So this still fails because the table doesn't exist. 80 00:03:26.280 --> 00:03:29.630 So let me add another transaction here in the middle. 81 00:03:29.630 --> 00:03:33.510 And we're gonna do call insert test data. 82 00:03:33.510 --> 00:03:35.760 Notice that this is different from functions 83 00:03:35.760 --> 00:03:40.240 that we use select for, here, we're using call instead. 84 00:03:40.240 --> 00:03:41.330 So there we have it. 85 00:03:41.330 --> 00:03:42.240 That worked. 86 00:03:42.240 --> 00:03:45.010 We now can select from this test data. 87 00:03:45.010 --> 00:03:48.830 Something to note though, is that usually you'll want to put 88 00:03:48.830 --> 00:03:51.630 a commit in here to make sure that the result 89 00:03:51.630 --> 00:03:53.960 of this transaction is saved. 90 00:03:53.960 --> 00:03:57.408 And DB fiddle is actually handling the transaction for us 91 00:03:57.408 --> 00:04:00.070 whenever we run one of these blocks. 92 00:04:00.070 --> 00:04:01.920 So that's why it wasn't necessary in here. 93 00:04:01.920 --> 00:04:04.410 But normally it will be necessary. 94 00:04:04.410 --> 00:04:07.570 This is also the reason why if you try to copy this 95 00:04:07.570 --> 00:04:11.940 and call it again, in here, you get an error in DB Fiddle, 96 00:04:11.940 --> 00:04:13.066 but you wouldn't normally, 97 00:04:13.066 --> 00:04:15.830 because it wraps the entire thing around a transaction. 98 00:04:15.830 --> 00:04:17.970 And then when you try to commit the transaction 99 00:04:17.970 --> 00:04:21.170 twice in here after ending it, it doesn't like that. 100 00:04:21.170 --> 00:04:25.140 And so in DB fiddle, you can't put two procedure calls 101 00:04:25.140 --> 00:04:27.670 in the same block, but you can put them in different blocks. 102 00:04:27.670 --> 00:04:29.270 And that's okay. 103 00:04:29.270 --> 00:04:30.830 The reason why we're getting an error here, 104 00:04:30.830 --> 00:04:32.650 which is the next thing I wanted to get to, 105 00:04:32.650 --> 00:04:36.050 is because when you call the procedure two times in a row, 106 00:04:36.050 --> 00:04:37.890 the table already exists. 107 00:04:37.890 --> 00:04:40.620 So obviously, for something like inserting test data, 108 00:04:40.620 --> 00:04:44.943 we should first drop the table if exists on test data. 109 00:04:45.970 --> 00:04:47.460 And that is gonna take care of dropping it 110 00:04:47.460 --> 00:04:48.460 if it already exists. 111 00:04:48.460 --> 00:04:50.400 And now, this will work fine. 112 00:04:50.400 --> 00:04:51.330 Just like with functions. 113 00:04:51.330 --> 00:04:53.840 There's a lot more to learn about stored procedures. 114 00:04:53.840 --> 00:04:56.440 They can have parameters so you can pass in arguments. 115 00:04:56.440 --> 00:04:58.240 You can have transactions save points, 116 00:04:58.240 --> 00:05:01.660 you can roll back transactions, and much more. 117 00:05:01.660 --> 00:05:03.780 I'd recommend reading through the official documentation, 118 00:05:03.780 --> 00:05:05.130 although it treats functions 119 00:05:05.130 --> 00:05:06.380 and procedures exactly the same. 120 00:05:06.380 --> 00:05:08.116 So maybe not the best place. 121 00:05:08.116 --> 00:05:12.560 But PostgreSQL tutorial also has a whole section dedicated 122 00:05:12.560 --> 00:05:14.450 to just stored procedures. 123 00:05:14.450 --> 00:05:16.450 And it has a lot of examples and things like that. 124 00:05:16.450 --> 00:05:19.700 So I'm gonna link you to those in case you're interested. 125 00:05:19.700 --> 00:05:21.130 All right, that's everything for this video. 126 00:05:21.130 --> 00:05:22.440 Thank you for watching. 127 00:05:22.440 --> 00:05:23.510 Thanks for joining me in this one. 128 00:05:23.510 --> 00:05:25.193 I'll see you in the next one.