How to check if a table exists in a given schema

user_742402 0
Viewed 254701

Postgres 8.4 and greater databases contain common tables in public schema and company specific tables in company schema.
company schema names always start with 'company' and end with the company number.
So there may be schemas like:

public
company1
company2
company3
...
companynn

An application always works with a single company.
The search_path is specified accordingly in odbc or npgsql connection string, like:

search_path='company3,public'

How would you check if a given table exists in a specified companyn schema?

eg:

select isSpecific('company3','tablenotincompany3schema')

should return false, and

select isSpecific('company3','tableincompany3schema')

should return true.

In any case, the function should check only companyn schema passed, not other schemas.

If a given table exists in both public and the passed schema, the function should return true.
It should work for Postgres 8.4 or later.

search-path information-schema postgresql sql database
4 Answers

For PostgreSQL 9.3 or less...Or who likes all normalized to text

Three flavors of my old SwissKnife library: relname_exists(anyThing), relname_normalized(anyThing) and relnamechecked_to_array(anyThing). All checks from pg_catalog.pg_class table, and returns standard universal datatypes (boolean, text or text[]).

/**
 * From my old SwissKnife Lib to your SwissKnife. License CC0.
 * Check and normalize to array the free-parameter relation-name.
 * Options: (name); (name,schema), ("schema.name"). Ignores schema2 in ("schema.name",schema2).
 */
CREATE FUNCTION relname_to_array(text,text default NULL) RETURNS text[] AS $f$
     SELECT array[n.nspname::text, c.relname::text]
     FROM   pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace,
            regexp_split_to_array($1,'\.') t(x) -- not work with quoted names
     WHERE  CASE
              WHEN COALESCE(x[2],'')>'' THEN n.nspname = x[1]      AND c.relname = x[2]
              WHEN $2 IS NULL THEN           n.nspname = 'public'  AND c.relname = $1
              ELSE                           n.nspname = $2        AND c.relname = $1
            END
$f$ language SQL IMMUTABLE;

CREATE FUNCTION relname_exists(text,text default NULL) RETURNS boolean AS $wrap$
  SELECT EXISTS (SELECT relname_to_array($1,$2))
$wrap$ language SQL IMMUTABLE;

CREATE FUNCTION relname_normalized(text,text default NULL,boolean DEFAULT true) RETURNS text AS $wrap$
  SELECT COALESCE(array_to_string(relname_to_array($1,$2), '.'), CASE WHEN $3 THEN '' ELSE NULL END)
$wrap$ language SQL IMMUTABLE;
user_2879480
user_156034770
Related