Elegant way of handling PostgreSQL exceptions?

Question

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

Answer 1

Exception handling in PL/pgSQL

Generally, plpgsql 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 this with plain SQL.

Each BEGIN block can optionally include an EXCEPTION clause for handling exceptions, but functions that need to trap exceptions are considerably more expensive, so it's best to avoid exceptions a priori.

More information:

• The manual on how to trap errors (handle exceptions) in PL/pgSQL

• Example: Is SELECT or INSERT in a function prone to race conditions?

• Search for related answers on SO

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) 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
$func$ LANGUAGE plpgsql;

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 parameters are case sensitive text values. That's subtly different from how identifiers in SQL statements are handled. If you never double-quote identifiers, pass lower case names and you are fine.

• 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 1
     FROM   information_schema.tables 
     WHERE  table_schema = _schema
     AND    table_name = _tbl
  );
  

  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