How can I determine if a table exists in the current search_path with PLPGSQL?

I'm writing a setup script for an application that's an addon for another application, so I want to check if the tables for the other application exist. If not, I want to give the user a useful error. However, I don't know what schema will be holding the tables.

DO LANGUAGE plpgsql $
BEGIN
    PERFORM 1
    FROM
        pg_catalog.pg_class c
        JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE
        n.nspname = current_setting('search_path')
        AND c.relname = 'foo'
        AND c.relkind = 'r'; -- not sure if I actually need this or not...

    IF NOT FOUND THEN
        RAISE 'This application depends on tables created by another application';
    END IF;
END;
$;

However, current_setting('search_path') returns a TEXT containing "$user",public by default, which isn't terribly useful.

The only other thing I can think of is to try selecting from the table and catch the exception. It would do the job, but I don't think it is very elegant and I've read that it is expensive to use (though maybe that would be ok in this scenario since I'm only running it once?).

Quick (and dirty?)

In Postgres 9.4 or newer use to_regclass():

SELECT to_regclass('foo');

Returns NULL if there is no relation of that name in the search path.

In Postgres 9.3 or older (or any version) use a cast to regclass:

SELECT 'foo'::regclass;

This raises an exception, if the object is not found!

See:

• How to check if a table exists in a given schema

If 'foo' is found, the oid is returned - which is represented as text. That's the relation name, schema-qualified according to the current search path and double-quoted where necessary.

If the relation is not found you can be sure it does not exist anywhere in the search path - or not at all for a schema-qualified name (schema.foo).

If it's found there are two shortcomings:

-
The search includes implicit schemas of the search_path, namely pg_catalog and pg_temp. But you may want to exclude temp and system tables for your purpose. (?)

-
A cast to regclass finds any relation (table-like object) in the system catalog pg_class: table, index, view, sequence etc. Not just a regular table. Other objects of the same name might produce a false positive.

Slow and sure (still fast, really)

We are back to something like your query. But don't use current_setting('search_path'), which returns the bare setting. Use the dedicated system information function current_schemas(). The manual:

current_schemas(boolean) name[]

names of schemas in search path, optionally including implicit schemas

"$user" in the search_path is resolved smartly. If no schema with the name of the current SESSION_USER exists, it resolves to nothing. You can additionally output implicit schemas (pg_catalog and possibly pg_temp) - but I assume not for the case at hand, so:

DO 
$do$
BEGIN
   IF EXISTS (
      SELECT  -- list can be empty
      FROM   pg_catalog.pg_class c
      JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
      WHERE  n.nspname = ANY(current_schemas(FALSE))
      AND    n.nspname NOT LIKE 'pg_%'  -- exclude system schemas!
      AND    c.relname = 'foo'
      AND    c.relkind = 'r')           -- you probably need this
   THEN
      RAISE 'This application depends on tables created by another application';
   END IF;
END
$do$;

db<>fiddle here

Old sqlfiddle

StackExchange Database Administrators Q#86050, answer score: 24