PostgreSQL provides several system schemas, such as information_schema, pg_catalog, etc., which can be used to check the existence of a table in a database. However, using these schemas often requires executing multiple lines of code every time we need to perform the check. To facilitate this process, users can create a custom function that encapsulates the table existence check and call it whenever needed. This approach improves coding efficiency and enhances the overall performance of the program.

This post illustrates how to check the table’s existence using a user-defined function in Postgres.

How to Check if a Table Exists in Postgres Database Using a User-Defined Function?

The user-defined functions allow us to create custom logic according to specific requirements. Using user-defined functions, users can check the existence of a particular Postgres table.

Let’s head into practical examples to understand this concept appropriately.

Example: Checking the Table’s Existence Using a User-defined Function

In the following example, a user-defined function named check_existence is created that accepts the table name and schema name as arguments. It checks the existence of the specified table and returns true or false based on the table’s existence:

CREATE FUNCTION check_existence(name_of_schema TEXT, name_of_table text) 
RETURNS Boolean AS $$
BEGIN
RETURN EXISTS (
   SELECT 1
   FROM information_schema.tables
   WHERE table_schema = name_of_schema
   AND table_name = name_of_table
);
END;
$$ LANGUAGE plpgsql;

The below snippet demonstrates that the desired user-defined function has been created successfully:

Now to check the existence of a specific table, all you have to do is call the “check_existence” function and pass the table’s name and schema name as arguments to it:

SELECT check_existence('public', 'emp_bio');

The output snippet shows that the selected table exists in the public schema:

Let’s call the “check_existence” function one more time and pass it a table name that doesn’t exists:

SELECT check_existence('public', 'employee_bio');

The output snippet confirms that the specified table does not exist:

That’s all about checking the table’s existence in Postgres using a user-defined function.

Conclusion

In PostgreSQL, a user-defined function can be created that accepts the table name and schema name as arguments. Once a function is created, it can be invoked with the schema name and table name. The function will check the existence of the specified table and return true or false based on the table’s existence. This post has illustrated a complete process of checking the table’s existence using a user-defined function.