Elegant way of handling PostgreSQL exceptions?
Exception handling in PL/pgSQL
PL/pgSQL code is always wrapped into a BEGIN ... END
block. That can be inside the body of a DO
statement or a function. Blocks can be nested inside - but they cannot exist outside, don't confuse it with plain SQL.
Each block can optionally contain an EXCEPTION
clause for handling exceptions, but functions that need to trap exceptions are more expensive, so it's best to avoid exceptions a priori. Postgres needs to prepare for the possibility of rolling back to a point in the transaction before the exception happened, similar to an SQL SAVEPOINT
. The manual:
A block containing an
EXCEPTION
clause is significantly more
expensive to enter and exit than a block without one. Therefore, don't
useEXCEPTION
without need.
Example:
- Is SELECT or INSERT in a function prone to race conditions?
How to avoid an exception in the example
A DO
statement can't return anything. Create a function that takes table and schema name as parameters and returns whatever you want:
CREATE OR REPLACE FUNCTION f_tbl_value(_tbl text, _schema text = 'public')
RETURNS TABLE (value text)
LANGUAGE plpgsql AS
$func$
DECLARE
_t regclass := to_regclass(_schema || '.' || _tbl);
BEGIN
IF _t IS NULL THEN
value := ''; RETURN NEXT; -- return single empty string
ELSE
RETURN QUERY EXECUTE
'SELECT value FROM ' || _t; -- return set of values
END IF;
END
$func$;
Call:
SELECT * FROM f_tbl_value('my_table');
Or:
SELECT * FROM f_tbl_value('my_table', 'my_schema');
Assuming you want a set of rows with a single text
column or an empty string if the table does not exist.
Also assuming that a column value
exists if the given table exists. You could test for that, too, but you didn't ask for that.
Both input parameters are only case sensitive if double-quoted. Just like identifiers are handled in SQL statements.
The schema name defaults to 'public'
in my example. Adapt to your needs. You could even ignore the schema completely and default to the current search_path
.
to_regclass()
is new in Postgres 9.4. For older versions substitute:
IF EXISTS (
SELECT FROM information_schema.tables
WHERE table_schema = _schema
AND table_name = _tbl
) THEN ...
This is actually more accurate, because it tests exactly what you need. More options and detailed explanation:
- Table name as a PostgreSQL function parameter
Always defend against SQL injection when working with dynamic SQL! The cast to regclass
does the trick here. More details:
- How to check if a table exists in a given schema
Is there a way to catch exception of a deferred constraint in pgsql?
You can set deferrable constraints to IMMEDIATE
just for your transaction without "turning immediate mode on" (without changing the constraint definition).
That's exactly what this separate command SET CONSTRAINTS
is for:
SET CONSTRAINTS child_pid_fk IMMEDIATE;
Or if you don't know constraint name(s):
SET CONSTRAINTS ALL IMMEDIATE;
The manual:
SET CONSTRAINTS
sets the behavior of constraint checking within the current transaction.
Bold emphasis mine.
And:
When
SET CONSTRAINTS
changes the mode of a constraint fromDEFERRED
toIMMEDIATE
, the new mode takes effect retroactively:
any outstanding data modifications that would have been checked at the
end of the transaction are instead checked during the execution of theSET CONSTRAINTS
command. If any such constraint is violated, theSET CONSTRAINTS
fails (and does not change the constraint mode).
Thus,SET CONSTRAINTS
can be used to force checking of constraints
to occur at a specific point in a transaction.
Exactly what you need.
More Elegant Exception Handling Than Multiple Catch Blocks?
In my opinion, a bunch of "ugly" catch blocks IS the best way to handle that situation.
The reason I prefer this is that it is very explicit. You are explicitly stating which exceptions you want to handle, and how they should be handled. Other forms of trying to merge handling into more concise forms lose readability in most cases.
My advice would be to stick to this, and handle the exceptions you wish to handle explicitly, each in their own catch block.
Cleanly setting the role when doing DDL from inside a PLPgSQL function
Use SET LOCAL ROLE
.
The documentation says:
The effects of SET LOCAL last only till the end of the current transaction, whether committed or not.
Unless you start an explicit transaction before calling the function, that should do what you want.
Related Topics
Query to Check Overlapping Ranges in SQL Server
How to Collapse The Rows of a Select into a String
Sql Server Table Locks in Long Query - Solution: Nolock
I Am Trying to Copy a File, But Getting Error Message
Tsql - Some | Any Why Are They Same with Different Names
Postgresql - Conditional Ordering
How to Get String After Character Oracle
Sql Server Stored Procedure and Execute in VB.NET
Why Do I Get an Open Transaction When Just Selecting from a Database View
Hql Join - Path Expected for Join! Hibernate
List All SQL Columns with Max Length and Greatest Length
Select All Projects That Have Matching Tags
Sql Server Reverse Order After Using Desc
What Is Srid 0 for Geometry Columns