CODEWITHSUNDEEP
sqlpostgresqlplpgsql
alt-f4
alt-f4

Reputation: 2306

How can I catch exceptions within a postgresql function?

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.

Upvotes: 1

Views: 2581

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246453

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

Upvotes: 3

Related Questions