Sample Header Ad - 728x90

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

13 votes
4 answers
35964 views
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?).
Asked by cimmanon (325 rep)
Dec 12, 2014, 06:29 PM
Last activity: Jun 9, 2021, 01:33 PM