Check if column exists when there are multiple tables with same name in different schemas (PSQL 8.2)

In order to check if a column exists I can easily use something similar to this:

SELECT attname FROM pg_attribute 
WHERE attrelid = (SELECT oid FROM 开发者_如何学JAVApg_class WHERE relname = 'YOURTABLENAME') 
AND attname = 'YOURCOLUMNNAME';

However, I run into problems with

SELECT oid FROM pg_class WHERE relname = 'YOURTABLENAME'

When there are several tables with the same name spread out over different schemas as it returns the OID's of all the tables with that name. How do I check if a table within a specific schema contains the column I am after? I'm using Postgres 8.2.

8.2 supports information_schema views. Something along these lines should work. You'll have to supply your own database, schema, table, and column names, of course.

select * 
from information_schema.columns
where table_catalog = 'sandbox'
and table_schema = 'public'
and table_name = 'calendar'
and column_name = 'iso_year';

Generic solution, for checking if table or column exists, is using functions (see below). Here I use also a function to parse the table name.

PS: the easy way to check table is using in your function the ::regclass, see the optional table_exists_v2() function.

CREATE FUNCTION tname_split(
  -- Verify the table name, sort, use defaults if necessary, and returns
  -- array with indexes: 1=complete name, 2=schema name, 3=table name.
  p_tabname varchar,                 -- table name or full name (schema.table)
  p_schema varchar DEFAULT 'public'  -- default schema name
) RETURNS varchar[] AS $func$
DECLARE 
  p integer;
BEGIN
  IF (p_schema IS NULL) THEN p_schema:='public'; END IF;
  p := strpos(p_tabname,'.'); -- check schema
  IF p=0 AND p_schema='' THEN
    RAISE EXCEPTION 'ERROR, schema name default can not be empty';
  ELSEIF p=0 THEN 
    RETURN ARRAY[p_schema||'.'||p_tabname,p_schema,p_tabname];
  ELSEIF p=1 THEN 
    RAISE EXCEPTION 'ERROR, table name initiating by dot';
  ELSE
    RETURN ARRAY[p_tabname] || string_to_array(p_tabname, '.')::varchar[]; 
  END IF;
END;
$func$ LANGUAGE plpgsql;

CREATE FUNCTION column_exists(
  -- 
  -- Check if a column exists in a table. Returns true if yes.
  -- 
  p_colname varchar,
  p_tname varchar,             -- table name or full name (schema.table)
  p_schema varchar DEFAULT NULL  -- default schema name
) RETURNS BOOLEAN AS $func$
DECLARE
  t varchar[]; 
BEGIN
  t = lib.tadm_tname_split(p_tname,p_schema);
  PERFORM 1 FROM information_schema.COLUMNS 
  WHERE column_name=$1 AND table_schema=t[2] AND table_name=t[3];
  RETURN FOUND;
END;
$func$ LANGUAGE plpgsql;


CREATE FUNCTION table_exists(
  -- 
  -- Check if a table exists, returns true if yes, false if not.
  -- 
  p_tname varchar,                -- table name or full name (schema.table)
  p_schema varchar DEFAULT 'public',  -- default schema name
) RETURNS BOOLEAN AS $func$
DECLARE
  t varchar[]; 
BEGIN
  t = lib.tadm_tname_split(p_tname,p_schema);
  RETURN EXISTS (SELECT tablename FROM pg_tables WHERE schemaname=t[2] AND tablename=t[3]);
END;
$func$ LANGUAGE plpgsql;

CREATE FUNCTION table_exists_v2(p_tname varchar) RETURNS boolean AS $f$
DECLARE
  tmp regclass;
BEGIN
    tmp := p_tname::regclass; -- do nothing, only parsing regclass
    RETURN true;
    EXCEPTION WHEN SQLSTATE '3F000' THEN
       RETURN false;
END;
$f$ LANGUAGE plpgsql;