How can I catch exceptions within a postgresql function?

Question

I am initializing functions as part of an ETL pipeline; however, one of the functions is dependant to operate on a certain table, which has not yet been created by the time this function has been initialized.

Illustration:

   CREATE OR REPLACE FUNCTION some_schema.my_func(parameter_1 BIGINT)
   RETURNS TEXT
    AS
    $$
    SELECT foo
    FROM non_existent_schema.non_existent_schema AS my_table  -- will cause error as this relation does not yet exist 
    WHERE my_table.bar = parameter_1
;
$$ LANGUAGE sql;

Section 42.6.8 in the documentation (Trapping Errors) discusses exception handling but using BEGIN statements (I am not sure where to include a BEGIN or if it is relevant to my case).

My question is, how can I avoid having this error, and if I would want to silence that Exception, what is the right way to do it.

note: I am a beginner with writing functions in Postgres.

score 2 · Accepted Answer · edited Apr 07 '20 at 12:16

You cannot do that in an SQL function, because SQL does not have procedural code. You need to use a different procedural language, for example PL/pgSQL:

CREATE FUNCTION some_schema.my_func(parameter_1 BIGINT) RETURNS TEXT
LANGUAGE plpgsql AS
$$BEGIN
    RETURN (SELECT foo
            FROM non_existent_schema.non_existent_schema AS my_table
            WHERE my_table.bar = parameter_1);
EXCEPTION
    WHEN undefined_table THEN
        NULL;  -- ignore
END;$$;

How can I catch exceptions within a postgresql function?

1 Answers1