-: Hi Guys, and Welcome back.
In this video we're going to talk about stored procedures
in PostgreSQL and how they are different from functions.
And really the key difference is that a stored procedure
has access to transactions and transaction control,
and it cannot return values.
However, a function is the opposite.
It doesn't have transaction control,
but it can return values.
So let's take a look at an example.
Here we've got some test data
that we're adding into our table.
Let's say that we wanna run this every time
that we run our applications tests.
So we're gonna be calling this over and over again.
We might be tempted to create a function to do this
or something like that.
But it's actually better to create a procedure
so that all of this can run inside a transaction
so that it runs all in one go essentially.
So let's create a procedure for this.
Creating a procedure is very similar to creating a function.
And indeed in PostgreSQL, they are treated very much
the same, but the word is slightly different.
We're gonna create procedure,
and then we're gonna give it a name,
in this case, insert test data.
Oh, data, there we go.
You can put in parameters inside the brackets
just like we can with functions.
Then we're gonna as dollar-dollar.
And then at the end, we're gonna do dollar-dollar
language SQL, just like that.
Okay, so now if I run this,
you'll see that we get a couple errors.
The first one is understandable.
Select star from test data and fails
because although we defined the procedure, we never ran it.
But why is there an error here?
It says, relation test data does not exist.
Insert into test data failed.
Well, why did it try to insert into test data
if we haven't run the procedure yet?
And that is because when you use language SQL,
we forgot a semicolon here that we should probably include.
When you use language SQL, it tries to validate the contents
of the procedure when you define it.
That is sub-optimal, I would say because clearly
many procedures that you're gonna define
are going to be creating data or you know,
creating tables and things like that.
And you don't wanna validate those
when you create the procedure, you wanna validate those
when you run the procedure.
And so this is a limitation of the plain SQL language.
So instead, we can use plpgsql.
This is the parallel PostgreSQL language
that is very similar to SQL,
but it does have a few improvements.
This is the language that the official PostgreSQL
documentation recommends for procedures.
And it's also a bit more powerful,
it can do a bunch more stuff than just plain old SQL.
So now if we run this, we get our result here.
The error in grey table occurs because PostgreSQL is aware
that we haven't started a transaction before trying
to run SQL code.
So we do have to do that.
We do begin in order to start the transaction
and in order to terminate the transaction.
Notice that in plpgsql begin does not use
a semicolon at the end.
That's just a difference between the two languages.
If you were using SQL, you would
have a semicolon after begin.
Okay, so now that is happy, and it will create our table
when we run the procedure.
But we haven't actually ran the procedure.
So this still fails because the table doesn't exist.
So let me add another transaction here in the middle.
And we're gonna do call insert test data.
Notice that this is different from functions
that we use select for, here, we're using call instead.
So there we have it.
That worked.
We now can select from this test data.
Something to note though, is that usually you'll want to put
a commit in here to make sure that the result
of this transaction is saved.
And DB fiddle is actually handling the transaction for us
whenever we run one of these blocks.
So that's why it wasn't necessary in here.
But normally it will be necessary.
This is also the reason why if you try to copy this
and call it again, in here, you get an error in DB Fiddle,
but you wouldn't normally,
because it wraps the entire thing around a transaction.
And then when you try to commit the transaction
twice in here after ending it, it doesn't like that.
And so in DB fiddle, you can't put two procedure calls
in the same block, but you can put them in different blocks.
And that's okay.
The reason why we're getting an error here,
which is the next thing I wanted to get to,
is because when you call the procedure two times in a row,
the table already exists.
So obviously, for something like inserting test data,
we should first drop the table if exists on test data.
And that is gonna take care of dropping it
if it already exists.
And now, this will work fine.
Just like with functions.
There's a lot more to learn about stored procedures.
They can have parameters so you can pass in arguments.
You can have transactions save points,
you can roll back transactions, and much more.
I'd recommend reading through the official documentation,
although it treats functions
and procedures exactly the same.
So maybe not the best place.
But PostgreSQL tutorial also has a whole section dedicated
to just stored procedures.
And it has a lot of examples and things like that.
So I'm gonna link you to those in case you're interested.
All right, that's everything for this video.
Thank you for watching.
Thanks for joining me in this one.
I'll see you in the next one.