17
votes
Accepted
How do I reraise an exception in a PL/pgSQL EXCEPTION block?
You can use RAISE without any parameters. This is documented on the Errors and Messages page:
The last variant of RAISE has no parameters at all. This form can only be used inside a BEGIN block's ...
- 1,612
7
votes
Best practices for committing a transaction in SQL Server where TRY CATCH is used
The best way I have found to do this is the following code:
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION
/*
Code goes here
*/
COMMIT TRANSACTION
END TRY
BEGIN CATCH
...
- 5,560
6
votes
What is the F301: the CORRESPONDING clause in query expression?
In a nutshell, CORRESPONDING was introduced into the SQL standard to make the syntax more in the spirit of the relational model (RM).
A full answer would inevitable involve a discussion about how the ...
- 2,612
5
votes
Accepted
How to make ON CONFLICT work for compound foreign key columns?
I think you may be misunderstanding what a CONFLICT is. A CONFLICT is a violation of uniqueness, basically the row that is being added should not be added because another row with the same values ...
- 770
4
votes
Best practices for committing a transaction in SQL Server where TRY CATCH is used
Option A is the correct choice. It is possible for all statements in a transaction to work and then the actual COMMIT to fail, so you keep the COMMIT inside your TRY block so that any failure of the ...
- 9,872
4
votes
How can I manage an exception generated by Postgis?
You can wrap the PostGIS function ST_Contains() in a plpgsql (or other PL) function of your own and trap the exception there. Like:
CREATE OR REPLACE FUNCTION f_ST_Contains(geometry, geometry)
...
- 172k
4
votes
Accepted
Display line number where error occurred in DB2 stored procedure
If you are on a currently supported version of Db2 for LUW (11.1 or 11.5) you can use either DBMS_UTILITY.FORMAT_CALL_STACK() or DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() in your signal handler to log ...
- 25k
4
votes
How to make ON CONFLICT work for compound foreign key columns?
If I understand correctly you want to insert the given row only if the FK constraint is satisfied - and do nothing otherwise, in particular do not raise an exception.
This is no "UPSERT", i.e. not a ...
- 172k
3
votes
Nested stored procedures and catch blocks - ERROR_PROCEDURE() issue
Plus, it is a bug, correct?
A documentation bug, perhaps. I'm submitting a doc PR to clean up some misleading language.
ERROR_PROCEDURE "returns the name of the stored procedure or trigger where an ...
- 45.8k
3
votes
Accepted
MySQL error message
The error you are encountering has been previously stored in the MySQL bug tracking system as
Bug #85807 Shutting down server: Unhandled exception
The recommendation is to upgrade MySQL ...
- 17.3k
2
votes
Accepted
Does MySQL close cursor if stored procedure encounters an exception?
use Approach #1:
according to http://www.brainbell.com/tutorials/MySQL/Working_With_Cursors.htm
If you do not explicitly close a cursor, MySQL will close it automatically when the END statement is ...
- 36
2
votes
Accepted
How to use user defined exception - PostgreSQL Function
Trapping and raising errors
To trap errors, use only one EXCEPTION clause in PL/pgSQL code. It can have multiple WHEN clauses. (But you seem to need only one. See below.)
You can work with error codes ...
- 172k
2
votes
Accepted
Do a lot of error checks affect query performance and is it bad practice?
I'm hard-pressed to see how an IF statement before the "meat" of the procedure that is just doing simple tests on the parameters would produce a bad plan.
Unless something really odd is going on, I ...
- 20.2k
2
votes
Accepted
how to convert from raiserror to throw in the current context?
I think this is what you mean? This is the closest you can get AFAIK.
--Catch any errors, include stored proc name for stack trace & cheeky link to SE
BEGIN CATCH;
IF (XACT_STATE()) = -1 ...
- 10.9k
2
votes
How to get error code of an exception type without raising it?
I don't know whether this is possible. In my application I define all errors in an generic PL/SQL package like this:
CREATE OR REPLACE PACKAGE Errors IS
E_ItemNotFound CONSTANT INTEGER := -20010;
...
- 2,923
1
vote
Accepted
Why does inserting a value into a temp table's BINARY(8) column cause an error when casting the same value to BINARY(8) does not?
As @mustaccio noted, it's because your value is 9 bytes long. CAST will truncate the value for you if it's too long. See below -
DROP TABLE IF EXISTS #QueryHashes;
CREATE TABLE #QueryHashes
(
...
- 301
1
vote
Accepted
Insert values to table only if first Insert was successful
Assuming no concurrent writes on the involved tables, this simpler function should cover everything you describe:
CREATE OR REPLACE FUNCTION public.new_conversation(INOUT _conv_id text, _members jsonb,...
- 172k
1
vote
Nested stored procedures and catch blocks - ERROR_PROCEDURE() issue
It doesn't seem bug of ERROR_PPROCEDURE() function. Instead, it's (you can say) limitation of ERROR_MESSAGE() & ERROR_PROCEDURE() function. Error at any level of nesting is an error in current ...
- 1,931
1
vote
Accepted
How to use COLUMN and TABLE options in a RAISE statement
It depends on the client-side verbosity. When using psql, issuing
\set VERBOSITY verbose
before calling the statement would caused the error message to include:
TABLE NAME: accounts
COLUMN NAME: ...
- 30.8k
1
vote
Accepted
What is the F301: the CORRESPONDING clause in query expression?
Syntax
From the SQL: 2011 spec (also in SQL-92), it is documented like this,
<corresponding spec> ::=
CORRESPONDING [ BY <left paren> <corresponding column list> <right paren&...
- 62.1k
1
vote
Accepted
How can I RAISE_APPLICATION_ERROR and do an INSERT in the same trigger?
Log type operations are usually done via AUTONOMOUS_TRANSACTION procedures.
Don't have a logging package, get one from GitHub.
https://github.com/OraOpenSource/Logger
- 4,804
1
vote
Accepted
How can I manage an exception generated by Postgis?
I think the best way is filtering the query or prefixing the data using commands like:
ST_IsValidReason, ST_IsValid, ST_MakeValid.
http://postgis.refractions.net/docs/ST_IsValidReason.html
- 1,163
Only top scored, non community-wiki answers of a minimum length are eligible