Officially, PostgreSQL only has "functions". Trigger functions are sometimes referred to as "trigger procedures", but that usage has no distinct meaning. Internally, functions are sometimes referred to as procedures, such as in the system catalog pg_proc
. That's a holdover from PostQUEL. Any features that some people (possibly with experience in different database systems) might associate with procedures, such as their relevance to preventing SQL injections or the use of output parameters, also apply to functions as they exist in PostgreSQL.
Now, when people in the PostgreSQL community talk about "stored procedures" or "real stored procedures", however, they often mean a hypothetical feature of a function-like object that can start and stop transactions in its body, something that current functions cannot do. The use of the term "stored procedure" in this context appears to be by analogy to other database products. See this mailing list thread for a vague idea.
In practice, however, this distinction of function versus procedure in terms of their transaction-controlling capabilities is not universally accepted, and certainly many programmers without database bias will take a Pascal-like interpretation of a procedure as a function without return value. (The SQL standard appears to take a middle ground, in that a procedure by default has a different transaction behavior than a function, but this can be adjusted per object.) So in any case, and especially when looking at questions on Stack Exchange with a very mixed audience, you should avoid assuming too much and use clearer terms or define the properties that you expect.
The TRY...CATCH
block is causing the execution of the stored procedure to terminate before it has a chance to return the values in #TEMP.
Try this stored proc:
IF OBJECT_ID('dbo.SomeProc') IS NOT NULL
DROP PROCEDURE dbo.SomeProc;
GO
CREATE PROCEDURE [dbo].[SomeProc]
AS
BEGIN
CREATE TABLE #TEMP
(
ID INT NOT NULL
);
INSERT INTO #TEMP
VALUES (1);
INSERT INTO #TEMP
VALUES (1/0);
PRINT (N'Run the SELECT');
SELECT ID
FROM #TEMP;
END
GO
Running it, like this, without a TRY...CATCH
, allows all the statements in the proc to run:
EXEC dbo.SomeProc;
The output:
Msg 8134, Level 16, State 1, Procedure SomeProc, Line 12
Divide by zero error encountered.
The statement has been terminated.
Run the SELECT
However, if you run it inside a TRY...CATCH
:
BEGIN TRY
EXEC dbo.SomeProc;
END TRY
BEGIN CATCH
PRINT (ERROR_MESSAGE());
END CATCH
You see only the error message:
Divide by zero error encountered.
The PRINT (N'Run the SELECT');
never runs, and indeed the SELECT ID FROM #TEMP;
never runs either. Hence no rows are returned, and nothing can be inserted into your #TEMPTABLE
From the MSDN documentation on TRY...CATCH:
If there is an error in the code that is enclosed in a TRY block, control passes to the first statement in the associated CATCH block.
As always, the devil is in the details. TRY...CATCH
always aborts the code inside the BEGIN TRY...END TRY
code block if any error over severity 10 occurs that does not close the database connection. Execution is immediately passed into the BEGIN CATCH...END CATCH
block, even if this means aborting code in a stored proc.
Be aware that if an error occurs on any row the entire insert will not happen. The only reason you're seeing this error, and a row being inserted, is because you have two insert statements, one that runs to completion, and one that throws an error. Take for instance:
IF OBJECT_ID('dbo.SomeProc') IS NOT NULL
DROP PROCEDURE dbo.SomeProc;
GO
CREATE PROCEDURE [dbo].[SomeProc]
AS
BEGIN
CREATE TABLE #TEMP
(
ID INT NOT NULL
);
INSERT INTO #TEMP
VALUES (1);
INSERT INTO #TEMP
VALUES (1)
, (2)
, (3)
, (4)
, (1/0);
PRINT (N'Run the SELECT');
SELECT ID
FROM #TEMP;
END
GO
Using this stored proc, either inside a TRY...CATCH
or with no TRY...CATCH
block, will only result in a single row being inserted into the #TEMPTABLE
- none of the rows in the 2nd insert will be present in the output.
Best Answer
The SQL standard gives you a guideline:
If your exception matches one of the SQLSTATEs in Appendix A of the PostgreSQL documentation, simply use that SQLSTATE.
If you need to use your own SQLSTATE, let it begin with any of
5
to9
orI
toZ
.If you need to define a custom warning, use an SQLSTATE that starts with
01
and whose third character is any of5
to9
orI
toZ
.