13

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

Improve this question

4 Answers 4

Reset to default
24

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:

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:

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

Improve this answer
3
  • May I know why is SELECT to_regclass('foo'); considered to be dirty? Commented Nov 4, 2021 at 15:51
  • 1
    @tukusejssirs: Because of the shortcommings I mentioned at the bottom of the paragraph. Commented Nov 4, 2021 at 15:54
  • fwiw, to include a schema: SELECT to_regclass('schema.tablename'); Commented Feb 8, 2023 at 17:47
1

You can convert the config value to an array and replace the $user with the current user name. The array can then be used in the where condition:

where n.nspname = any(string_to_array(replace(current_setting('search_path'), '$user', current_user), ','))
Improve this answer
0 
./sshi.sh vb20deployment controller <<'HERE' export PGPASSWORD="postgres" cd logu/postgresql/bin row=1 tableArray=(table1 table2 table3 table4 table5 table6) for (( x=0 ; x<=5 ; x++)) ; do ./psql.bin --port=5432 --username=postgres --host=hostname.rds.amazonaws.com --dbname=mydb -c "SELECT * FROM information_schema.tables WHERE '${tableArray[$x]}' = table_name" | while read -a Record ; do row=$((row + 1)) if [[ $row -gt 3 ]]; then echo ${Record[4]} fi done done HERE
Improve this answer
-1

In case you'll need this functionality a lot, there probably is a sense to create a function which will do the check. Something like this:

DROP FUNCTION IF EXISTS table_exists(table_name varchar); CREATE FUNCTION table_exists(table_name varchar) RETURNS bool AS $$ BEGIN BEGIN EXECUTE format('SELECT * FROM %I LIMIT 0', table_name); RETURN true; EXCEPTION WHEN undefined_table THEN RETURN false; END; END $$ LANGUAGE plpgsql;

And than you can use it like this:

SELECT table_exists('my_table_name')

Or in pgPL/SQL:

IF table_exists('my_table_name') THEN ...
Improve this answer
4
  • 1
    This is open to SQL injection. Don't concatenate object names in a dynamic SQL string without proper quoting. Use %L instead of %s here. Also, the function may return false and a table of the given name may still exist, just outside the current search_path. So "table_exists" seems misleading. Maybe "table_exists_in_current_search_path"? Finally, a function with an EXCEPTION clause is considerably more expensive than the query of the OP. Commented Jun 9, 2021 at 1:04
  • @ErwinBrandstetter Good point about SQL injection, thanks, I've update the code (though, it doesn't work with %L, had to change to %I). Commented Jun 9, 2021 at 9:44
  • @ErwinBrandstetter regarning exception (just asking) — is it considered to avoid exceptions in Postres PL/SQL? I have some background on semi-professional development in Oracle PL/SQL — and there exceptions were used as ordinary part of logic. Commented Jun 9, 2021 at 9:57
  • 1
    Yes, %I (identifier), not %L (literal), sorry. Yes, use an EXCEPTION clause to trap errors in PL/pgSQL. (Not "PL/SQL", the name is different fro the Oracle procedural language.) But only if you have to. See: dba.stackexchange.com/a/233394/3684. If you use this function (which I wouldn't, honestly), consider a cheaper LIMIT 0 instead of LIMIT 1. And be aware, that you might find a view or sequence instead of a table ... Commented Jun 9, 2021 at 11:02

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.