In PostgreSQL, I would like to create a safe-wrapping mechanism which returns empty result if an exception occurs. Consider the following:
SELECT * FROM myschema.mytable;
I could do the safe-wrapping in the client application:
try {
result = execute_query('SELECT value FROM myschema.mytable').fetchall();
}
catch(pg_exception) {
result = []
}
But could I do such a thing in SQL directly? I would like to make the following code work, but it seems like it should by put into DO $$ ... $$
block and here I'm getting lost.
BEGIN
SELECT * FROM myschema.mytable;
EXCEPTION WHEN others THEN
SELECT unnest(ARRAY[]::TEXT[])
END
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:
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:
Always defend against SQL injection when working with dynamic SQL! The cast to regclass
does the trick here. More details: