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

Quick and dirty

In Postgres 9.4+ use

SELECT to_regclass('foo');

Returns NULL if the identifier is not found in the search path.
In Postgres 9.3 or older use a cast to regclass:

SELECT 'foo'::regclass;

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

If 'foo' is found, the oid is returned in its text representation. That's just the table name, schema-qualified according to the current search path and double-quoted where necessary.

If the object 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:

1. 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. (?)

2. A cast to regclass works for all objects in the system catalog pg_class: indexes, views, sequences etc. Not just tables. You seem to be looking for a regular table exclusively. However, you'll probably have problems with other objects of the same name, too. Details:

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

Slow and sure

We are back to your query, but don't use current_setting('search_path'), which returns the bare setting. Use the dedicated system information function current_schemas(). Per documentation:

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 SESSION_USER exists, the schema is not returned to begin with. Also, depending on what you want exactly, you can additionally output implicit schemas (pg_catalog and possibly pg_temp) - but I assume you don't want those for the case at hand, so use:

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

SQL Fiddle, demonstrating all except for the last DO statement.
_{SQL Fiddle (JDBC) has problems with DO statements containing termination characters.}