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.

filiprem · Accepted Answer · 2020-04-07 12:16:31Z

3

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;$$;

edited Apr 7, 2020 at 12:16

filiprem

7,2141 gold badge34 silver badges43 bronze badges

answered Apr 7, 2020 at 7:48

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I catch exceptions within a postgresql function?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related