Quick and dirty
In Postgres 9.4+ use
Returns NULL if the identifier is not found in the search path.
In Postgres 9.3 or older use a cast to
This raises an exception, if the object is not found!
'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 (
If it's found there are two shortcomings:
The search includes implicit schemas of the search_path, namely
pg_temp. But you may want to exclude temp and system tables for your purpose. (?)
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:
Slow and sure
We are back to your query, but don't use
, which returns the bare setting. Use the dedicated system information function
current_schemas(). Per documentation:
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:
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
RAISE 'This application depends on tables created by another application';
SQL Fiddle, demonstrating all except for the last
SQL Fiddle (JDBC) has problems with
DO statements containing termination characters.