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;
精彩评论