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

Alkaline

DO cannot return anything. You have to use a stored procedure for that (with language plpgsql -- for exception handling). -- also no rows returned does not equal to a single row returned, with an empty array in it (i.e. in js, this means [] != [{col1:[]}]). – Brunelleschi

@posz Sorry for that, I added unnest to my example to produce the desired behavior (don't know how to do this more elegantly). Anyway, do I have to declare procedure each time I am executing a query with exception handling? Is there no other option? – Alkaline

So you want to defend against any and alll exceptions or are you just afraid the table might not exist? And what do you want to return? A single value from the single column value? Or a set of rows? And is this for one hard-coded table name or for a variety of possible table names? – Servomechanism

@Alkaline no, if you want to do only exception handling over simple queries, this is usually done by the client, not the server. plpgsql's exception handling is mainly for stored "logic". But what type of exception do you afraid of? Maybe, there is an alternative. – Brunelleschi

@posz Actually, in my specific case, I'm quite concerned about race conditions. I'm using SELECT ('myschema','mytable') IN (SELECT table_schema,table_name FROM information_schema.tables); to check whether the table exists, and if it does, I'm selecting its rows. But it often happens that the table ceases to exist before the second query is executed (i.e., the idiom "if table exists, select its contents" does not work). This is why I wish to simply return empty result if an exception occurs. – Alkaline

Somebody edited by question to "...if table does not exist", which is NOT I what I meant. I was asking about elegant ways of exception handling. – Alkaline

@Tregoreg: Sorry, seems like I have been assuming too much when editing the title. – Servomechanism

Answer 1

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 use EXCEPTION 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

Servomechanism

Thanks for your comprehensive answer, but I was asking for exception handling rather than checking whether table exists, because I am facing race conditions (not only in this specific case of eventually nonexistent table). I saw exception handling in official psql documentation for emulating UPSERT: postgresql.org/docs/current/static/… This is what led me to ask for elegant exception handling in general. – Alkaline

@Tregoreg: Added a bit about exception handling in general. – Servomechanism

I finally ended up with BEGIN .. END inside DO block for my INSERT statements where race conditions can easily occur and I actually don't need to select anything. I did some benchmarking and it seems that exception handling in such a way only costs about 1ms, which is fully satisfactory for me. @ErwinBrandsetter thanks for your clear explanation and emphasizing the difference between plain SQL and plpgsql, that's what I was missing. – Alkaline

@ErwinBrandstetter I tried serval times. I even dropped link to internet friends. Seems f_tbl_value function cannot be created. ERROR: syntax error at end of input LINE 13: $func$ LANGUAGE plpgsql; I don't know why. – Manrope

@JianHe: I fixed the syntax error. – Servomechanism

Can you explain your statement, "... functions that need to trap exceptions are considerably more expensive"? Do you mean more expensive whether or not exceptions actually happen? Isn't it the whole point of an exception handler, that it costs virtually nothing if there are no actual exceptions? – Tiny

@Tom: Entering a block with an EXCEPTION clause is more expensive. I updated with a quote from the manual. – Servomechanism

Answer 2

If you are selecting only one column then the COALESCE() function should be able to do the trick for you

SELECT COALESCE( value, '{}'::text[] ) FROM myschema.mytable

If you require more rows you may require to create a function with types.

Viyella

Actually, my example might be a bit misleading.. I meant a more general, where the tables could actually be missing (and hence throwing exception).. What I am asking about is exception handling. – Alkaline