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
Last activity: Jun 9, 2021, 01:33 PM